Voici pourquoi SQLite est parfait pour votre travail quotidien. Peu importe que vous soyez développeur, analyste, testeur, administrateur ou chef de produit.
Pour commencer, quelques faits connus:
SQLite est le système de gestion de base de données le plus utilisé au monde et est inclus dans tous les systèmes d'exploitation courants.
Fonctionne sans serveur.
Pour les développeurs, il est directement intégré à l'application.
Pour tout le monde - une console pratique (REPL) dans un seul fichier (sqlite3.exe sous Windows, sqlite3 sous Linux / macOS).
Console, import et export
La console est une fonctionnalité qui tue de SQLite: un outil d'analyse de données plus puissant qu'Excel, et beaucoup plus simple que n'importe quel pandas. Les données du CSV sont chargées avec une seule commande, la table est créée automatiquement:
> .import --csv city.csv city
> select count(*) from city;
1117
Les fonctionnalités SQL de base sont prises en charge et la console affiche le résultat dans une belle table. Il existe également des fonctionnalités SQL avancées, mais nous en parlerons ultérieurement.
select
century || '- ' as dates,
count(*) as city_count
from history
group by century
order by century desc;
┌──────────┬────────────┐
│ dates │ city_count │
├──────────┼────────────┤
│ 21- │ 1 │
│ 20- │ 263 │
│ 19- │ 189 │
│ 18- │ 191 │
│ 17- │ 137 │
│ 16- │ 79 │
│ 15- │ 39 │
│ 14- │ 38 │
│ 13- │ 27 │
│ 12- │ 44 │
│ 11- │ 8 │
│ 10- │ 6 │
│ 9- │ 4 │
│ 5- │ 1 │
│ 3- │ 1 │
└──────────┴────────────┘
Un tas de formats d'exportation de données: sql, csv, json, même markdown et html. Tout se fait avec quelques commandes:
.mode json
.output city.json
select city, foundation_year, timezone from city limit 10;
.shell cat city.json
[{"city":"","foundation_year":1969,"timezone":"UTC+3"},
{"city":"","foundation_year":1857,"timezone":"UTC+3"},
{"city":"-","foundation_year":1830,"timezone":"UTC+7"},
{"city":"","foundation_year":1913,"timezone":"UTC+7"},
{"city":"","foundation_year":1730,"timezone":"UTC+7"},
{"city":"","foundation_year":1846,"timezone":"UTC+7"},
{"city":"","foundation_year":1709,"timezone":"UTC+7"},
{"city":"","foundation_year":1942,"timezone":"UTC+7"},
{"city":"","foundation_year":1748,"timezone":"UTC+7"},
{"city":"","foundation_year":1736,"timezone":"UTC+7"}]
Travailler avec JSON de manière native
Il n'y a rien de plus pratique que SQLite pour analyser et transformer JSON. Vous pouvez sélectionner des données directement à partir du fichier, comme s'il s'agissait d'un tableau normal. Ou chargez-le dans une table et sélectionnez-le à partir de là - comme vous préférez.
select
json_extract(value, '$.code') as code,
json_extract(value, '$.name') as name,
json_extract(value, '$.rate') as rate,
json_extract(value, '$.default') as "default"
from
json_each(readfile('currency.sample.json'))
;
┌──────┬───────────────────┬────────────┬─────────┐ │ code │ name │ rate │ default │ ├──────┼───────────────────┼────────────┼─────────┤ │ AZN │ │ 0.023107 │ 0 │ │ BYR │ │ 0.034966 │ 0 │ │ EUR │ │ 0.011138 │ 0 │ │ GEL │ │ 0.0344 │ 0 │ │ KGS │ │ 1.131738 │ 0 │ │ KZT │ │ 5.699857 │ 0 │ │ RUR │ │ 1.0 │ 1 │ │ UAH │ │ 0.380539 │ 0 │ │ USD │ │ 0.013601 │ 0 │ │ UZS │ │ 142.441417 │ 0 │ └──────┴───────────────────┴────────────┴─────────┘
Peu importe l'étalement du JSON, vous pouvez choisir les attributs de n'importe quelle imbrication:
select
json_extract(value, '$.id') as id,
json_extract(value, '$.name') as name
from
json_tree(readfile('industry.sample.json'))
where
path like '$[%].industries'
;
┌────────┬──────────────────────┐ │ id │ name │ ├────────┼──────────────────────┤ │ 7.538 │ - │ │ 7.539 │ - │ │ 7.540 │ │ │ 9.399 │ │ │ 9.400 │ │ │ 9.401 │ │ │ 43.641 │ │ │ 43.646 │ │ │ 43.647 │ │ └────────┴──────────────────────┘
Opérations CTE et set
, Common Table Expressions ( WITH
) , . ( parent_id
) — WITH
. «» .
with recursive tmp(id, name, level) as (
select id, name, 1 as level
from area
where parent_id is null
union all
select
area.id,
tmp.name || ', ' || area.name as name,
tmp.level + 1 as level
from area
join tmp on area.parent_id = tmp.id
)
select * from tmp;
┌──────┬─────────────────────────────────────┬───────┐ │ id │ name │ level │ ├──────┼─────────────────────────────────────┼───────┤ │ 113 │ │ 1 │ │ 1 │ , │ 2 │ │ 1586 │ , │ 2 │ │ 1588 │ , , │ 3 │ │ 78 │ , , │ 3 │ │ 212 │ , , │ 3 │ │ ... │ ... │ ... │ └──────┴─────────────────────────────────────┴───────┘
? : UNION
, INTERSECT
, EXCEPT
.
select employer_id
from employer_area
where area_id = 1
except
select employer_id
from employer_area
where area_id = 2;
? — :
alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.13)
else
salary_from
end
);
, :
select
substr(name, 1, 40) as name,
salary_net
from vacancy
where
salary_currency = 'RUR'
and salary_net is not null
limit 10;
┌──────────────────────────────────────────┬────────────┐
│ name │ salary_net │
├──────────────────────────────────────────┼────────────┤
│ - (Delphi) │ 40000 │
│ Scala / Java ( Senio │ 60000 │
│ Java / Kotlin Developer │ 150000 │
│ 1 │ 150000 │
│ C# │ 53097 │
│ 1 │ 80000 │
│ Java - (Middle, Senior) │ 100000 │
│ C#/ .NET │ 70796 │
│ / QA engineer ( │ 45000 │
│ │ 17699 │
└──────────────────────────────────────────┴────────────┘
.
? : , , , . , . , ( ).
.load sqlite3-stats select count(*) as book_count, cast(avg(num_pages) as integer) as mean, cast(median(num_pages) as integer) as median, mode(num_pages) as mode, percentile_90(num_pages) as p90, percentile_95(num_pages) as p95, percentile_99(num_pages) as p99 from books;
┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘
. SQLite - . , — . .
, . , :
. . , :
with slots as (
select
num_pages/100 as slot,
count(*) as book_count
from books
group by slot
),
max as (
select max(book_count) as value
from slots
)
select
slot,
book_count,
printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar
from slots, max
order by slot;
┌──────┬────────────┬────────────────────────────────┐
│ slot │ book_count │ bar │
├──────┼────────────┼────────────────────────────────┤
│ 0 │ 116 │ ********* │
│ 1 │ 254 │ ******************** │
│ 2 │ 376 │ ****************************** │
│ 3 │ 285 │ ********************** │
│ 4 │ 184 │ ************** │
│ 5 │ 90 │ ******* │
│ 6 │ 54 │ **** │
│ 7 │ 41 │ *** │
│ 8 │ 31 │ ** │
│ 9 │ 15 │ * │
│ 10 │ 11 │ * │
│ 11 │ 12 │ * │
│ 12 │ 2 │ * │
│ 13 │ 5 │ * │
│ 14 │ 3 │ * │
│ 15 │ 1 │ * │
│ 17 │ 1 │ * │
│ 18 │ 2 │ * │
└──────┴────────────┴────────────────────────────────┘
SQLite ( — ). INSERT
240 . CSV ( ) — 2 .
.load sqlite3-vsv create virtual table temp.blocks_csv using vsv( filename="ipblocks.csv", schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)", columns=10, header=on, nulls=on );
.timer on
insert into blocks
select * from blocks_csv;
Run Time: real 5.176 user 4.716420 sys 0.403866
select count(*) from blocks;
3386629
Run Time: real 0.095 user 0.021972 sys 0.063716
, SQLite , . . write-ahead log ( ) . — , .
SQLite . , sqlite.org SQLite , (~200 ). 700 , 95% .
,
, «» . .
SQLite : json json_extract()
:
create table currency(
body text,
code text as (json_extract(body, '$.code')),
name text as (json_extract(body, '$.name'))
);
create index currency_code_idx on currency(code);
insert into currency
select value
from json_each(readfile('currency.sample.json'));
explain query plan
select name from currency where code = 'RUR';
QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)
. WITH RECURSIVE
, :
:
create virtual table books_fts
using fts5(title, author, publisher);
insert into books_fts
select title, author, publisher from books;
select
author,
substr(title, 1, 30) as title,
substr(publisher, 1, 10) as publisher
from books_fts
where books_fts match 'ann'
limit 5;
┌─────────────────────┬────────────────────────────────┬────────────┐ │ author │ title │ publisher │ ├─────────────────────┼────────────────────────────────┼────────────┤ │ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │ │ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │ │ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │ │ Daniel Defoe │ Robinson Crusoe │ Ann Arbor │ │ Ann Thwaite │ Waiting for the Party: The Lif │ David R. G │ └─────────────────────┴────────────────────────────────┴────────────┘
in-memory ? :
db = sqlite3.connect(":memory:")
:
db = sqlite3.connect("file::memory:?cache=shared")
( PostgreSQL). UPSERT
, UPDATE FROM
generate_series()
. R-Tree . , fuzzy- . SQLite «» .
, SQLite . , !
, SQLite — @sqliter