SQLite3: встраиваемая SQL база данных

SQLite — это СУБД, которая работает внутри вашей программы как библиотека: нет сервера, который нужно запускать, нет пользователей и настроек. Вся база данных — таблицы, индексы и данные — это один обычный файл. sqlite3 — это третья версия формата этого файла и имя его оболочки командной строки.

На этой странице мы работаем с оболочкой, а потом открываем ту же базу из двух языков программирования. Если вы пишете на Python, статья SQLite3 Python шаг за шагом показывает модуль Python. Проверено в Ubuntu 24.04.5 LTS с sqlite3 3.45.1, Python 3 и PHP 8.2.

Что такое SQLite

Одна база — один файл. Скопировали файл — скопировали базу.
Нет серверного процесса: программа читает и пишет файл через библиотеку SQLite.
Транзакции: изменение записывается целиком или не записывается совсем, в том числе после сбоя или отключения питания.
Много читателей, один писатель: пока транзакция пишет, остальные пишущие ждут.

Поэтому SQLite — обычный выбор для данных, которые принадлежат одной программе: браузеры и мобильные приложения хранят в ней настройки и историю, настольные программы — документы, тесты — временные данные, небольшие сайты — содержимое.

Установка оболочки sqlite3

sudo apt install sqlite3

sqlite3 --version

3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)

Пакет содержит только оболочку. Программы используют библиотеку SQLite, которую большинство языков поставляют вместе с драйвером базы, поэтому программе оболочка не нужна.

Создать базу и таблицу

sqlite3 создаёт файл базы при первой записи. SQL после имени файла выполняется сразу:

sqlite3 shop.db "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL);"

sqlite3 shop.db "INSERT INTO items (name, price) VALUES ('Keyboard', 49.9), ('Mouse', 19.5), ('Monitor', 189);"

Столбец, объявленный как INTEGER PRIMARY KEY, нумерует строки сам. База — небольшой файл:

ls -l shop.db

-rw-r--r-- 1 andrei andrei 8192 Nov 2 23:28 shop.db

Без SQL после имени файла sqlite3 shop.db открывает интерактивную оболочку с приглашением sqlite>. Вводите SQL с точкой с запятой в конце, а для выхода — .quit.

Запросы и форматы вывода

sqlite3 shop.db 'SELECT * FROM items;'

1|Keyboard|49.9 2|Mouse|19.5 3|Monitor|189.0

По умолчанию столбцы разделены символом |. -header -column добавляет названия и выравнивает столбцы:

sqlite3 -header -column shop.db 'SELECT * FROM items;'

id name price -- -------- ----- 1 Keyboard 49.9 2 Mouse 19.5 3 Monitor 189.0

-box рисует таблицу:

sqlite3 -box shop.db 'SELECT name, price FROM items WHERE price < 100;'

┌──────────┬───────┐ │ name │ price │ ├──────────┼───────┤ │ Keyboard │ 49.9 │ │ Mouse │ 19.5 │ └──────────┴───────┘

а -json выводит строки для скриптов:

sqlite3 -json shop.db 'SELECT name, price FROM items WHERE price > 50;'

[{"name":"Monitor","price":189.0}, {"name":"Webcam","price":59.8999999999999985}]

Этот вывод получен в конце страницы, после того как импорт CSV добавил веб-камеру. 59.9 превращается в 59.8999999999999985, потому что REAL — двоичное число с плавающей точкой: оно хранит ближайшее двоичное значение, а вывод JSON и .dump печатают все его цифры. Денежные суммы храните в копейках в столбце INTEGER.

Таблицы и схема

sqlite3 shop.db .tables

items

sqlite3 shop.db '.schema items'

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

Команды, которые начинаются с точки, относятся к оболочке, а не к SQL. В интерактивной оболочке они работают так же, а .help показывает их все.

Импорт и экспорт CSV

Два новых товара пришли в CSV-файле со строкой заголовков:

cat new_items.csv

name,price Headset,35 Webcam,59.9

Импортируйте его в новую таблицу. Названия столбцов sqlite3 берёт из первой строки:

sqlite3 shop.db '.import --csv new_items.csv staging'

sqlite3 shop.db '.schema staging'

CREATE TABLE IF NOT EXISTS "staging"( "name" TEXT, "price" TEXT);

Все столбцы импортированной таблицы имеют тип TEXT. Скопируйте строки в настоящую таблицу, где они получат свои типы и id, и удалите промежуточную таблицу:

sqlite3 shop.db 'INSERT INTO items (name, price) SELECT name, price FROM staging; DROP TABLE staging;'

sqlite3 -header -column shop.db 'SELECT * FROM items;'

id name price -- -------- ----- 1 Keyboard 49.9 2 Mouse 19.5 3 Monitor 189.0 4 Headset 35.0 5 Webcam 59.9

Две ловушки прямого импорта: в файле два столбца, а в items три, а с --skip 1 в ещё не существующую таблицу sqlite3 пропускает заголовок и берёт названия столбцов из первой строки данных.

Экспорт работает в обратную сторону:

sqlite3 -csv -header shop.db 'SELECT * FROM items;' > items.csv

cat items.csv

id,name,price 1,Keyboard,49.9 2,Mouse,19.5 3,Monitor,189.0 4,Headset,35.0 5,Webcam,59.9

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

.dump записывает всю базу в виде SQL:

sqlite3 shop.db .dump > shop.sql

cat shop.sql

PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL); INSERT INTO items VALUES(1,'Keyboard',49.8999999999999985); INSERT INTO items VALUES(2,'Mouse',19.5); INSERT INTO items VALUES(3,'Monitor',189.0); INSERT INTO items VALUES(4,'Headset',35.0); INSERT INTO items VALUES(5,'Webcam',59.8999999999999985); COMMIT;

Передайте его sqlite3, чтобы собрать из него новую базу:

sqlite3 restored.db < shop.sql

sqlite3 restored.db 'SELECT count(*) FROM items;'

.backup безопасно копирует базу, даже пока ею пользуется другая программа; обычный cp безопасен, только если в файл никто не пишет. Проверьте копию:

sqlite3 shop.db ".backup 'shop-backup.db'"

sqlite3 shop-backup.db 'PRAGMA integrity_check;'

SQLite из языков программирования

Любой язык с драйвером SQLite открывает тот же файл. Обе программы ниже читают shop.db из шагов выше и выводят товары дешевле 50.

Python: модуль sqlite3 входит в стандартную библиотеку.

vi read_items.py

import sqlite3 con = sqlite3.connect("shop.db") for name, price in con.execute("SELECT name, price FROM items WHERE price < ?", (50,)): print(name, price) con.close()

python3 read_items.py

Keyboard 49.9 Mouse 19.5 Headset 35.0

PHP: PDO с расширением pdo_sqlite.

vi read_items.php

<?php $db = new PDO('sqlite:shop.db'); $query = $db->prepare('SELECT name, price FROM items WHERE price < ?'); $query->execute([50]); foreach ($query as $row) { echo $row['name'], ' ', $row['price'], PHP_EOL; }

php read_items.php

Keyboard 49.9 Mouse 19.5 Headset 35

Строки те же, с одним отличием: PHP печатает число 35.0 как 35. Другие языки используют такие драйверы (на этой странице не проверялись):

Язык	Драйвер
C	сама библиотека SQLite, заголовок sqlite3.h
Java	JDBC-драйвер org.xerial:sqlite-jdbc
Go	database/sql с modernc.org/sqlite или github.com/mattn/go-sqlite3
Node.js	встроенный node:sqlite начиная с Node 22.5 или better-sqlite3
Rust	rusqlite
C#	Microsoft.Data.Sqlite

Когда нужна серверная СУБД

Много программ или пользователей пишут одновременно: SQLite допускает одного пишущего за раз.
База лежит на сетевом ресурсе: блокировки файлов через NFS или SMB ненадёжны, файл может повредиться.
Одну базу используют несколько серверов, или нужны учётные записи и права для каждого пользователя.

Тогда используйте СУБД с сервером, например PostgreSQL или MySQL.

Автор статьи: Клавдия Кондакова