The avatar for @asg017
Alex Garcia
Software engineer, available for freelance work! • he/him/his
Published
SQLite Blog
Edited
Sep 13, 2022
2 stars
3
SQLite Blog
A new Blog Series on SQLite and ToolingIntroducing sqlite-lines - a SQLite extension for reading files line-by-lineIntroducing sqlite-html: query, parse, and generate HTML in SQLiteIntroducing sqlite-http: A SQLite extension for making HTTP requestsScraping JSON, HTML, and ZIP Files with Pure SQLiteIntroducing sqlite-path: A SQLite extension for parsing and generating paths
Introducing sqlite-url: A SQLite extension for parsing and generating URLs
Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in RustIntroducing sqlite-xsv: The Fastest CSV Parser for SQLiteIntroducing sqlite-regex: A fast Regular Expression Extension for SQLiteMaking SQLite extensions pip install-ableIntroducing sqlite-vss: A SQLite Extension for Vector SearchMaking SQLite extensions npm install'able for Node.js, and on deno.land/x for DenoMaking SQLite extension gem install'able for Ruby Developerssqlite-vss v0.1.1 Annotated Release Notes
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
import { DatasetteClient } from "@ambassadors/datasette-client"
Insert cell
db = new DatasetteClient("https://sqlite-extension-examples.fly.dev/data")
Insert cell
Insert cell
db
select url_version(), url('https://alexgarcia.xyz/');
Insert cell
Insert cell
db
select * from washingtonpost_links;
Insert cell
Insert cell
db
select url_host(url), count(*)
from washingtonpost_links
where url_valid(url)
group by 1
order by 2 desc;
Insert cell
Insert cell
db
select url, text
from washingtonpost_links
where not url_valid(url)
order by 1
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select
path_at(url_path(url), 0) as news_section,
count(*)
from washingtonpost_links
-- filter out invalid URLs
where news_section is not null
group by 1
order by 2 desc
Insert cell
Insert cell
Insert cell
Insert cell
db
select
-- validate as a real date with sqlite's date() function
date(
-- get '2022-09-06' from 'https://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/'
printf('%s-%s-%s',
path_at(url_path(url), 1),
path_at(url_path(url), 2),
path_at(url_path(url), 3)
)
) as date,
count(*)
from washingtonpost_links
-- filter out invalid dates
where date is not null
group by 1
order by 1;
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select
distinct path_at(url_path(url), -1) as slug,
(
select json_group_array(line)
from lines(path_at(url_path(url), -1), '-')
) as slug_words
from washingtonpost_links
where path_at(url_path(url), 1) = '2022'
order by 1;
Insert cell
Insert cell
db
select
slug_words.line as word,
count(*)
from washingtonpost_links
join lines(path_at(url_path(url), -1), '-') as slug_words
where path_at(url_path(url), 1) = '2022'
group by 1
order by 2 desc
limit 15
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more