Антон Жиянов Profile picture
Mar 4, 2021 23 tweets 7 min read Read on X
SQLite — не игрушка! Один лайк — один факт, почему она отлично подойдет вам в повседневной работе. И неважно, разработчик вы, аналитик, тестировщик, админ или продакт-менеджер.
Для затравки несколько известных фактов. sqlite — самая распространенная СУБД в мире, встроена во все ОС. Она работает без сервера. Для разработчиков — встраивается прямо в приложение. Для всех остальных — удобная консоль (REPL) одним файлом (sqlite.exe на Windows).
Консоль — это киллер-фича sqlite: более мощный инструмент анализа данных, чем Excel, и сильно более простой, чем какой-нибудь pandas. Данные из CSV загружаются одной командой, таблица создается автоматически:

> .import --csv city.csv city
> select count(*) from city;
1117
Поддерживаются базовые SQL-фичи, а результат консоль умеет показывать в приятной табличке. Продвинутые SQL-фичи тоже есть, но о них лучше отдельно.

select
century || '-й век' as dates,
count(*) as city_count
from history
group by century
order by century desc; Image
Куча форматов выгрузки данных: sql, csv, json, даже markdown и html. Все делается парой команд:

> .mode json
> .output city.json
> select * from city; Image
Нет ничего удобнее sqlite для анализа и преобразования JSON. Можно селектить данные напрямую из файла, как будто это обычная таблица. Или загрузить в таблицу и селектить оттуда — как вам удобнее. Image
Неважно, насколько развесистый JSON — можно выбрать атрибуты любой вложенности:

select
json_extract(value, '$.id') as id,
json_extract(value, '$.name') as name
from
json_tree(readfile('industry.sample.json'))
where
path like '$[%].industries'
; Image
Разумеется, поддерживаются CTE (конструкция WITH) и джойны, тут даже примеры приводить не буду. А если данные иерархичные (таблица ссылается сама на себя через столбец вроде parent_id) — поможет рекурсивный WITH. Иерархию любого уровня можно «размотать» одним запросом. ImageImage
Множества? Нет проблем: UNION, INTERSECT, EXCEPT к вашим услугам.

select employer_id
from employer_area
where area_id = 1

except

select employer_id
from employer_area
where area_id = 2; Image
Хотите рассчитать один столбец на основании нескольких других? Пожалуйста — вычисляемые столбцы.

alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.13)
else
salary_from
end
); Image
Описательная статистика? Запросто: среднее, медиана, процентили, стандартное отклонение и вот это все. Правда, придется подключить библиотеку со стат. функциями, но это тоже одна команда (и один файл). Image
Лирическое отступление. SQLite традиционно бедна функциями по сравнению с каким-нибудь постгресом. Но их легко добавить, чем многие и занимаются — каждый кто во что горазд. Получается легкий бардак.
Поэтому я решил сделать нормальный набор библиотек с функциями, с разделением по предметной области и автоматической сборкой для всех ОС. Пока библиотек там немного, но скоро прибавится: github.com/nalgeon/sqlite…
А вот еще о статистике. Можно нарисовать распределение значений прямо в консоли. Смотрите, какая милота: ImageImage
И еще очень, очень много всего! Если интересно, лайкайте исходный твит, и продолжим ближе к вечеру. А, и подписывайтесь на t.me/sqliter, конечно
SQLite спокойно работает с десятками миллионов записей (с сотнями, говорят, тоже — но я не проверял). Обычные INSERT дают на моем ноуте около 240 тысяч записей в секунду. А если подключить исходный CSV как виртуальную таблицу (такая специальная фича) — еще в 2 раза быстрее. Image
Поддерживаются частичные индексы и индексы по выражениям, как в «больших» СУБД. Можно даже строить индексы на виртуальных столбцах.

Так SQLite можно превратить хоть в документную БД: хранить сырой json и строить индексы по json_extract() на нужных столбцах. Image
Можно и как графовую базу использовать. Тут уже придется либо злые WITH RECURSIVE использовать (не буду пугать вас примерами), либо добавить щепотку программирования: github.com/dpapathanasiou…
Полнотекстовый поиск из коробки!

create virtual table books_fts
using fts5(title, author, publisher);

insert into books_fts
select * from books;

select * from books_fts
where books_fts match 'sqlite OR postgres*'; Image
Среди разработчиков распространено мнение, что SQLite не подходит для веба, потому что поддерживает только одного клиента. Это миф. В режиме write-ahead log (стандартная фича современных СУБД) читателей может быть сколько угодно. Писатель — один, но часто больше и не надо.
SQLite отлично подходит для маленьких и средних сайтов и приложений. Например, сайт sqlite.org использует SQLite в качестве базы, не заморачиваясь с оптимизацией (~200 запросов на страницу). При этом у него 700К визитов в месяц, а работает быстрее 95% сайтов.
Хотите in-memory базу для промежуточных вычислений? Одна строчка кода на питоне:

db = sqlite3.connect(":memory:")

Можно даже обращаться к ней из нескольких соединений:

db = sqlite3.connect("file::memory:?cache=shared")
Есть навороченные оконные функции. UPSERT, UPDATE FROM и generate_series() как в постгресе. R-Tree индексы. Регекспы, fuzzy-поиск и гео. Но кажется, на сегодня хватит. Надеюсь, этот тред вдохновит кого-нибудь применить SQLite в своих задачах. Спасибо, что прочитали!

• • •

Missing some Tweet in this thread? You can try to force a refresh
 

Keep Current with Антон Жиянов

Антон Жиянов Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!

PDF

Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @nalgeon

Apr 22, 2020
Расскажу вам про транслитерацию (это когда Юлия превращается в Iuliia в загранпаспорте). В википедии тема не раскрыта, про прочие места вообще молчу. Пришло время сорвать покровы.
Транслит — это хрестоматийная ситуация «у нас 14 плохих стандартов, давайте придумаем ещё один». Весь 20 век солидные, уважаемые люди придумывали всё новые и новые стандарты транслитерации.
Получалось у них очень, очень плохо. Например, в загранпаспорте пишут «Юлия → Iuliia» не потому, что МИД хочет сделать вам больно, а потому что это международный стандарт ICAO Doc 9303 — Machine Readable Travel Documents.
Read 17 tweets

Did Thread Reader help you today?

Support us! We are indie developers!


This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Don't want to be a Premium member but still want to support us?

Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal

Or Donate anonymously using crypto!

Ethereum

0xfe58350B80634f60Fa6Dc149a72b4DFbc17D341E copy

Bitcoin

3ATGMxNzCUFzxpMCHL5sWSt4DVtS8UqXpi copy

Thank you for your support!

Follow Us!

:(