Jonnyan的原创笔记
alpine
alpine里python安装mssql笔记
Alpine linux如何配置和管理自定义服务
windows
window server2012远程授权重置
window获取本机所有IP
window远程桌面RDP加速方案
远程监控 Win10 资源占用
windows 下 mysql 区分大小写敏感问题
window下navicat无限试用脚本
win11恢复win10右键菜单样式
永久禁止windows更新
强制本地账户安装win10/11
sqlserver(2012)在线清理tempdb
Linux
解决openvpn的CRL has expired笔记
centos7.x配置时间服务器(chrony)
centos7.x下安装wireguard
解决influxdb的log日志输出位置
保存 iptable 规则并开机自动加载 | SA-Logs
kafka笔记
kafka的server.properties 配置文件参数说明
CentOS 和 RedHat 下 8 个最常用的 YUM 库
外网IP查询网站
VirtualBox Ubuntu20/centos7 命令行如何扩容分区磁盘
如何备份sqlite数据库
yum 安装 redis5/mq/consul
centos7.x 安装 docker-ce
zabbix4.2 的 yum+mariadb 方式部署安装
如何在 Linux 中查找最大的 10 个文件
mongodb 备份与还原操作
Linux 高频工具快速教程
yum 安装 influxdb/telegraf
ubuntu 14.04/16.04/18.04 yum 安装 zabbix-agent 教程
逃不掉的 mysql 数据库安装方式大全 yum rpm 源码
VIM 配置入门
find 命令结合 cp bash mv 命令使用的 4 种方式
Tomcat nginx log 日志按天分割切割
linux 和 pycharm 下终端彩色打印输出
centos5/6/7 下 yum 安装 zabbix-agent(被控端)
shell 脚本头,#!/bin/sh 与 #!/bin/bash 的区别.
electerm/tabby在执行screen命令后不显示滚动条
aws ec2 安装caddy2
No usable version of libssl was found
python
python virtualenv笔记
python配置文件INI/TOML/YAML/ENV的区别
python限制函数的执行时间
python里and和or的理解
SQLite is not a toy database | Anton Zhiyanov
四行代码实现 Python 管道 - Aber's blog
systemd管理虚拟环境Django+uwsgi+nginx配置教程
Linux shell命令创建python django用户
nginx子路径下反代运行多个django
django web 应用 runserver 模式下 cpu 占用高解决办法
解决 pip 安装模块报错 Cannot fetch index base URL http://pypi.python.org/simple/
docker
仅在首次启动时在Docker容器中运行命令
Docker多平台架构镜像构建
解决cadvisor监控内存值与docker stats命令值不一致问题
docker 清理指定日期之前的镜像
docker 部署 graylog 使用教程
docker 一键搭建 zerotier-moon 节点
alpine的docker镜像安装mysql/mariadb/redis
dockerfile 多阶段构建参考
Warning: Stopping docker.service, but it can still be activated by: docker.socket
jsonargsrecommended: json arguments recommended for entrypoint to prevent unintended behavior related to os signals (line 30)
nginx
Nginx限制并发连接数与下载速度
nginx仅允许域名访问禁止IP访问
Nginx 强制跳转 Https
nginx强制跳转https无限301循环问题
万字总结,带你全面系统的认识 Nginx
linux 下编译安装 nginx 完整版
解决 nginx 同端口强制跳转 https 配置 ssl 证书问题
nginx 关闭日志功能 access_log 关闭
基于 nginx 的 token 认证
杂记
小米手机MIUI12安装Google服务
使用sphinx+markdown+readthedocs+github来编写文档
N1由armbian直刷openwrt
N1安装docker版本的openwrt做旁路由
NUC10 i3/i5/i7系列开启局域网wol唤醒
威联通qnap安装nginx
威联通qnap配置开机自启动项
telegram bot python使用示例教程
四款paste临时文本分享平台
docker部署微力同步(verysync)
Android和IOS自部署通知程序
苹果M1如何科学上网
M1 mac iterm2配置lrzsz命令
漫威轮播
网件XR500/R7800刷机
DIY 编译 openwrt 固件
苹果 mac 版微软官方远程连接工具下载 Microsoft Remote Desktop For Mac
wireguard 实现 peer 互联, NAT to NAT
学习本来的样子
解决 aws ec2 的 centos7 设置时区无效
redis 问题优化
N1 如何完美刷入 armbian 系统教程
v2rayN 的 pac 简单规则
博客园 markdown 使用折叠语法和颜色标签
十年感悟之 python 之路
在浏览器输入 URL 回车后发生了什么?
grafana 里 prometheus 查询语法
国内开源镜像站点汇总
解决阿里云部署 office web apps ApplicationFailedException 报错问题
解决 mac 休眠睡眠异常耗电方法
jira 集成 fisheye 代码深度查看工具安装绿色版
阿里云 ecs 开启 x11 图形化桌面
markdown 完整语法规范 3.0 + 编辑工具介绍
pycharm 重置设置,恢复默认设置
[已解决]window 下 Can't connect to MySQL server on'localhost' (10061) 与无法启动 MYSQL 服务”1067 进程意外终止”
解决 xshell6 评估过期, 需采购问题
[已解决]pycharm 报错: AttributeError: module 'pip' has no attribute 'main'
[已解决]windows 下 python3.x 与 python2.7 共存版本 pip 使用报错问题
局域网共享工具总结
云策文档think配置https教程
MIUI12-14百度输入法小米版使用森林集皮肤办法
Jenkins 构建后通知到飞书
简易的openvpn安装
keychron V1键盘改键教程
caddy2配置SSE单向websock(How to proxy Server Sent Events caddy2)
cleanmymacx 一直要求输入密码问题解决
Mac配置鼠须管输入法(Rime)
sorry this adobe app is not available(mac版本的Photoshop)
caddy2配置websocks
解决 all DNS requests failed, first error: dns: bad rdata
小米hyperos系统关闭5G信号开关
tg每日自动签到
简单的路由器系统
telegram被封号怎么办
机器监控告警
zabbix
yum / 编译安装 Zabbix 5.0 LTS
zabbix 监控 AWS-SQS 队列
Zabbix-agent 端配置文件说明
Prometheus+grafana
prometheus+grafana安装和配置
node_exporter主机监控
cadvisor容器监控
redis_exporter监控
rabbitmq_exporter监控
consul_exporter监控
windows_exporter
Open-Falcon
falcon 数据丢失处理方法参考
日志监控告警
graylog
graylog 通过 python 实现钉钉 / 微信 / webhook 告警
loki+grafana
Loki简介
Loki安装
Loki查询语法
grafana面板pannel语法
内网穿透
frp(推荐一)
zerotier(推荐二)
zerotier充当网关实现内网互联,访问其它节点内网
一分钟自建zerotier-plant
tailscale(推荐三)
N2N
nps
anylink
OmniEdge
quickvlan(昆浪智能)
本文档发布于https://mrdoc.fun
-
+
首页
SQLite is not a toy database | Anton Zhiyanov
> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码, 原文地址 [antonz.org](https://antonz.org/sqlite-is-not-a-toy-database/) _English • [Russian](https://habr.com/ru/post/547448/) • [Spanish](https://sysarmy.com/blog/posts/sqlite-no-es-una-base-de-datos-de-juguete/)_ Whether you are a developer, data analyst, QA engineer, DevOps person, or product manager - SQLite is a perfect tool for you. Here is why. A few well-known facts to get started: * SQLite is the most common DBMS in the world, shipped with all popular operating systems. * SQLite is serverless. * For developers, SQLite is embedded directly into the app. * For everyone else, there is a convenient database console (REPL), provided as a single file (sqlite3.exe on Windows, sqlite3 on Linux / macOS). Console, import, and export --------------------------- The console is a killer SQLite feature for data analysis: more powerful than Excel and more simple than `pandas`. One can import CSV data with a single command, the table is created automatically: ``` > .import --csv city.csv city > select count(*) from city; ``` The console supports basic SQL features and shows query results in a nice ASCII-drawn table. Advanced SQL features are also supported, but more on that later. ``` select century || ' century' as dates, count(*) as city_count from history group by century order by century desc; ``` ``` ┌────────────┬────────────┐ │ dates │ city_count │ ├────────────┼────────────┤ │ 21 century │ 1 │ │ 20 century │ 263 │ │ 19 century │ 189 │ │ 18 century │ 191 │ │ 17 century │ 137 │ │ ... │ ... │ └────────────┴────────────┘ ``` Data could be exported as SQL, CSV, JSON, even Markdown and HTML. Takes just a couple of commands: ``` .mode json .output city.json select city, foundation_year, timezone from city limit 10; .shell cat city.json ``` ``` [ { "city": "Amsterdam", "foundation_year": 1300, "timezone": "UTC+1" }, { "city": "Berlin", "foundation_year": 1237, "timezone": "UTC+1" }, { "city": "Helsinki", "foundation_year": 1548, "timezone": "UTC+2" }, { "city": "Monaco", "foundation_year": 1215, "timezone": "UTC+1" }, { "city": "Moscow", "foundation_year": 1147, "timezone": "UTC+3" }, { "city": "Reykjavik", "foundation_year": 874, "timezone": "UTC" }, { "city": "Sarajevo", "foundation_year": 1461, "timezone": "UTC+1" }, { "city": "Stockholm", "foundation_year": 1252, "timezone": "UTC+1" }, { "city": "Tallinn", "foundation_year": 1219, "timezone": "UTC+2" }, { "city": "Zagreb", "foundation_year": 1094, "timezone": "UTC+1" } ] ``` If you are more of a BI than a console person - popular data exploration tools like [Metabase](https://www.metabase.com/), [Redash](https://redash.io/), and [Superset](https://superset.apache.org/) all support SQLite. Native JSON ----------- There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Or import data into the table and select from there. ``` select json_extract(value, '$.iso.code') as code, json_extract(value, '$.iso.number') as num, json_extract(value, '$.name') as name, json_extract(value, '$.units.major.name') as unit from json_each(readfile('currency.sample.json')) ; ``` ``` ┌──────┬─────┬─────────────────┬──────────┐ │ code │ num │ name │ unit │ ├──────┼─────┼─────────────────┼──────────┤ │ ARS │ 032 │ Argentine peso | peso │ │ CHF │ 756 │ Swiss Franc │ franc │ │ EUR │ 978 │ Euro │ euro │ │ GBP │ 826 │ British Pound │ pound │ │ INR │ 356 │ Indian Rupee │ rupee │ │ JPY │ 392 │ Japanese yen │ yen │ │ MAD │ 504 │ Moroccan Dirham │ dirham │ │ RUR │ 643 │ Russian Rouble │ rouble │ │ SOS │ 706 │ Somali Shilling │ shilling │ │ USD │ 840 │ US Dollar │ dollar │ └──────┴─────┴─────────────────┴──────────┘ ``` Doesn’t matter how deep the JSON is - you can extract any nested object: ``` 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 │ Internet provider │ │ 7.539 │ IT consulting │ │ 7.540 │ Software development │ │ 9.399 │ Mobile communication │ │ 9.400 │ Fixed communication │ │ 9.401 │ Fiber-optics │ │ 43.641 │ Audit │ │ 43.646 │ Insurance │ │ 43.647 │ Bank │ └────────┴──────────────────────┘ ``` CTEs and set operations ----------------------- Of course, SQLite supports Common Table Expressions (`WITH` clause) and `JOIN`s, I won’t even give examples here. If the data is hierarchical (the table refers to itself through a column like `parent_id`) - `WITH RECURSIVE` will come in handy. Any hierarchy, no matter how deep, can be ‘unrolled’ with a single query. ``` 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 │ ├──────┼──────────────────────────┼───────┤ │ 93 │ US │ 1 │ │ 768 │ US, Washington DC │ 2 │ │ 1833 │ US, Washington │ 2 │ │ 2987 │ US, Washington, Bellevue │ 3 │ │ 3021 │ US, Washington, Everett │ 3 │ │ 3039 │ US, Washington, Kent │ 3 │ │ ... │ ... │ ... │ └──────┴──────────────────────────┴───────┘ ``` Sets? No problem: `UNION`, `INTERSECT`, `EXCEPT` are at your service. ``` select employer_id from employer_area where area_id = 1 except select employer_id from employer_area where area_id = 2; ``` Calculate one column based on several others? Enter generated columns: ``` alter table vacancy add column salary_net integer as ( case when salary_gross = true then round(salary_from/1.04) else salary_from end ); ``` Generated columns can be queried in the same way as ‘normal’ ones: ``` select substr(name, 1, 40) as name, salary_net from vacancy where salary_currency = 'JPY' and salary_net is not null limit 10; ``` Math statistics --------------- Descriptive statistics? Easy: mean, median, percentiles, standard deviation, you name it. You’ll have to load an extension, but it’s also a single command (and a single file). ``` .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 │ └────────────┴──────┴────────┴──────┴─────┴─────┴──────┘ ``` **Note on extensions**. SQLite is missing a lot of functions compared to other DBMSs like PostgreSQL. But they are easy to add, which is what people do - so it turns out quite a mess. Therefore, I decided to make a consistent set of extensions, divided by domain area and compiled for major operating systems. There are few of them there yet, but more are on their way: [sqlean @ GitHub](https://github.com/nalgeon/sqlean/) More fun with statistics. You can plot the data distribution right in the console. Look how cute it is: ``` 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 │ * │ └──────┴────────────┴────────────────────────────────┘ ``` Performance ----------- SQLite works with hundreds of millions of records just fine. Regular `INSERT`s show about 240K records per second on my laptop. And if you connect the CSV file as a virtual table (there is an extension for that) - inserts become 2 times faster. ``` .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; Run Time: real 0.095 user 0.021972 sys 0.063716 ``` There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. This is a myth. In the write-ahead log mode (available since long ago), there can be as many concurrent readers as you want. There can be only one concurrent writer, but often one is enough. SQLite is a perfect fit for small websites and applications. [sqlite.org](https://sqlite.org/) uses SQLite as a database, not bothering with optimization (≈200 requests per page). It handles 700K visits per month and serves pages faster than 95% of websites I’ve seen. Documents, graphs, and search ----------------------------- SQLite supports partial indexes and indexes on expressions, as ‘big’ DBMSs do. You can build indexes on generated columns and even turn SQLite into a document database. Just store raw JSON and build indexes on `json_extract()`-ed columns: ``` 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 = 'EUR'; QUERY PLAN `--SEARCH TABLE currency USING INDEX currency_code_idx (code=?) ``` You can also use SQLite as a graph database. A bunch of complex `WITH RECURSIVE` will do the trick, or maybe you’ll prefer to add a bit of Python: [simple-graph @ GitHub](https://github.com/dpapathanasiou/simple-graph) Full-text search works out of the box: ``` 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 │ └─────────────────────┴────────────────────────────────┴────────────┘ ``` Maybe you need an in-memory database for intermediate computations? Single line of python code: ``` db = sqlite3.connect(":memory:") ``` You can even access it from multiple connections: ``` db = sqlite3.connect("file::memory:?cache=shared") ``` And so much more ---------------- There are fancy window functions (just like in PostgreSQL). `UPSERT`, `UPDATE FROM`, and `generate_series()`. R-Tree indexes. Regular expressions, fuzzy-search, and geo. In terms of features, SQLite can compete with any ‘big’ DBMS. There is also great tooling around SQLite. I especially like [Datasette](https://datasette.io/) - an open-source tool for exploring and publishing SQLite datasets. And [DBeaver](https://dbeaver.io/) is an excellent open-source database IDE with the latest SQLite versions support. I hope this article will inspire you to try SQLite. Thanks for reading! _Follow [@ohmypy](https://twitter.com/ohmypy) on Twitter to keep up with new posts 🚀_ [Comments on Hacker News](https://news.ycombinator.com/item?id=26580614)
Jonny
April 8, 2021, 12:58 p.m.
940
0 条评论
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
如遇文档失效,可评论告知,便后续更新!
【腾讯云】2核2G云服务器新老同享 99元/年,续费同价
【阿里云】2核2G云服务器新老同享 99元/年,续费同价(不要✓自动续费)
【腾讯云】2核2G云服务器新老同享 99元/年,续费同价
【阿里云】2核2G云服务器新老同享 99元/年,续费同价(不要✓自动续费)
Markdown文件
Word文件
PDF文档
PDF文档(打印)
分享
链接
类型
密码
更新密码
有效期