Типы данных в SQLite и их связь с Python

Типы данных в SQLite устроены иначе, чем в MySQL или PostgreSQL: тип привязан к самому значению, а объявленный тип столбца лишь подсказывает, во что его по возможности превратить. Ниже разобраны пять классов хранения, правила type affinity, хранение дат и логических значений, строгие таблицы STRICT и соответствие типов Python и SQLite в модуле sqlite3.

Пять классов хранения

Каждое значение в базе SQLite относится к одному из пяти классов хранения (storage class). Именно класс определяет, как значение записано на диске, и именно его возвращает функция typeof().

Диапазон INTEGER составляет от -9223372036854775808 до 9223372036854775807. Маленькие числа занимают меньше места: SQLite сама выбирает размер от 1 до 8 байт. REAL дает около 15-16 значащих цифр, поэтому для денежных сумм надежнее хранить целые копейки в INTEGER, чем рубли с дробной частью в REAL.

Что такое type affinity

В обычной таблице SQLite не запрещает записать строку в столбец INTEGER. Вместо жесткой проверки у каждого столбца есть affinity, то есть предпочтительный класс хранения. При вставке SQLite пытается преобразовать значение к этому классу, а если без потерь не получается, сохраняет его как есть.

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (a INTEGER, b TEXT, c NUMERIC, d)')
conn.execute("INSERT INTO t VALUES ('42', 42, '3.0', '42')")
row = conn.execute(
    'SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t'
).fetchone()
print(row)  # ('integer', 'text', 'integer', 'text')

Строка '42' в столбце INTEGER превратилась в число, число 42 в столбце TEXT стало строкой, а '3.0' в NUMERIC сохранилось как целое 3, потому что дробной части у значения нет. Столбец d объявлен без типа, поэтому значение записано без изменений.

Как SQLite определяет affinity по имени типа

SQLite не хранит список допустимых типов. Она смотрит на текст объявленного типа и применяет правила по порядку, останавливаясь на первом совпадении:

1. есть подстрока INT - affinity INTEGER;
2. есть CHAR, CLOB или TEXT - affinity TEXT;
3. есть BLOB или тип не указан - affinity BLOB, значение хранится как есть;
4. есть REAL, FLOA или DOUB - affinity REAL;
5. во всех остальных случаях - NUMERIC.

Отсюда несколько неочевидных следствий. Тип VARCHAR(3) получает affinity TEXT, но ограничение длины не проверяется: в такой столбец спокойно запишется строка из ста символов. Типы BOOLEAN, DATE, DECIMAL и STRING получают NUMERIC. А FLOATING POINT из-за подстроки INT внутри слова POINT неожиданно станет INTEGER.

Даты, время и логические значения

Отдельных типов для дат и булевых значений в SQLite нет. Для дат на выбор три формата, и встроенные функции date(), datetime() и strftime() понимают каждый из них.

На практике чаще всего выбирают текст в формате ISO. Главное - использовать один формат во всей таблице, иначе сравнения и сортировка дадут ошибочный результат.

from datetime import datetime

conn.execute('CREATE TABLE log (msg TEXT, created TEXT, is_error INTEGER)')
conn.execute('INSERT INTO log VALUES (?, ?, ?)',
             ('старт', datetime.now().isoformat(' ', 'seconds'), False))

msg, created, is_error = conn.execute('SELECT * FROM log').fetchone()
print(datetime.fromisoformat(created), bool(is_error))

Логические значения хранятся как INTEGER 0 и 1, а Python при записи сам превращает True и False в 1 и 0. При чтении обратно вернется число, поэтому его оборачивают в bool(). С версии Python 3.12 встроенные адаптеры для datetime в модуле sqlite3 объявлены устаревшими, поэтому даты лучше явно переводить в строку и обратно, как в примере выше. Подробнее о работе с датами в Python рассказано в статье про модуль datetime.

Строгие таблицы STRICT

Начиная с SQLite 3.37.0 (2021 год) таблицу можно объявить строгой, добавив слово STRICT после закрывающей скобки. В такой таблице разрешены только типы INT, INTEGER, REAL, TEXT, BLOB и ANY, а значение, которое не удается привести к типу столбца, вызывает ошибку.

conn.execute('CREATE TABLE price (id INTEGER PRIMARY KEY, value REAL) STRICT')
conn.execute("INSERT INTO price (value) VALUES ('19.90')")  # приведется к 19.9
try:
    conn.execute("INSERT INTO price (value) VALUES ('abc')")
except sqlite3.IntegrityError as e:
    print(e)  # cannot store TEXT value in REAL column price.value

Попытка объявить в строгой таблице столбец типа DATE или VARCHAR завершится ошибкой unknown datatype. Узнать версию библиотеки, с которой работает Python, можно через sqlite3.sqlite_version. Для новых проектов STRICT помогает поймать ошибки в данных на этапе записи, а не через полгода, когда отчет начнет выдавать странные суммы.

Сравнение и сортировка значений разных типов

Так как в одном столбце обычной таблицы могут оказаться значения разных классов, SQLite задает между ними порядок: NULL меньше любых чисел, числа меньше любого текста, текст меньше BLOB. INTEGER и REAL сравниваются между собой как обычные числа.

print(conn.execute("SELECT 10 < '9'").fetchone())  # (1,): число всегда меньше текста
print(conn.execute('SELECT 5 / 2, 5 / 2.0').fetchone())  # (2, 2.5)

Первый результат объясняет классическую ошибку: если цены по недосмотру записаны строками, сортировка и сравнения с числами начинают работать неправильно. Второй показывает, что деление двух INTEGER в SQLite целочисленное, в отличие от оператора / в Python 3.

Особый случай INTEGER PRIMARY KEY

Столбец, объявленный ровно как INTEGER PRIMARY KEY, становится псевдонимом встроенного rowid. Он принимает только целые числа, заполняется автоматически при вставке без значения и не тратит лишнего места. Вариант INT PRIMARY KEY так не работает: это обычный столбец с уникальным индексом. Про ключи и связи между таблицами подробно рассказано в материале о структуре таблицы базы данных.

Как выбрать тип столбца

Несколько практических правил, которые избавят от большинства проблем с типами:

• объявляйте типы явно, даже если SQLite их не требует: это документация схемы для вас и для других программистов;
• для идентификаторов используйте INTEGER PRIMARY KEY;
• деньги храните в INTEGER в минимальных единицах (копейках), а не в REAL;
• даты храните строками ISO 8601 в одном формате по всей базе;
• для файлов в BLOB проверьте размер: большие картинки обычно выгоднее хранить на диске, а в базе держать путь;
• в новых проектах включайте STRICT, если версия SQLite не ниже 3.37.

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

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

Ответы на вопросы, которые возникают при проектировании таблиц SQLite.

Есть ли в SQLite тип BOOLEAN?

Отдельного типа нет. Объявить столбец как BOOLEAN можно, но значения будут храниться как целые 0 и 1 с affinity NUMERIC.

Работает ли ограничение длины VARCHAR(255)?

Нет, число в скобках игнорируется. Чтобы ограничить длину, добавьте проверку CHECK(length(name) <= 255).

Как узнать, в каком типе реально хранится значение?

Используйте функцию typeof() в запросе: SELECT typeof(price) FROM goods. Она вернет null, integer, real, text или blob.

Какой тип выбрать для дробных чисел с точностью до копейки?

Надежнее хранить сумму в копейках в INTEGER и делить на 100 при выводе. REAL подвержен ошибкам округления двоичной дроби.