Awesome
sqlite-html
A SQLite extension for querying, manipulating, and creating HTML elements.
- Extract HTML or text from HTML with CSS selectors, like
.querySelector()
,.innerHTML
, and.innerText
- Generate a table of matching elements from a CSS selector, like
.querySelectorAll()
- Safely create HTML elements in a query, like
.createElement()
and.appendChild()
sqlite-html
's API is modeled after the official JSON1 SQLite extension.
This extension is written in Go, thanks to riyaz-ali/sqlite. While this library aims to be fast and efficient, it is overall slower than what a pure C SQLite extension could be, but in practice you may not notice much of a difference.
Usage
.load ./html0
select html_extract('<p> Anakin <b>Skywalker</b> </p>', 'b');
-- "<b>Skywalker</b>"
sqlite-html
is similar to other HTML scraping tools like BeautifulSoup (Python) or cheerio (Node.js) or nokogiri (Ruby). You can use CSS selectors to extract individual elements or groups of elements to query data from HTML sources.
For example, here we find all href
links in an index.html
file.
select
text as name,
html_attribute_get(anchors, 'a', 'href') as href
from html_each(readfile('index.html'), 'a') as anchors
We can also safely generate HTML with html_element
, modeled after React's React.createElement
.
select html_element('p', null,
'Luke, I am your',
html_element('b', null, 'father'),
'!',
html_element('img', json_object(
'src', 'https://images.dog.ceo/breeds/groenendael/n02105056_4600.jpg',
'width', 200
))
);
-- "<p>Luke, I am your<b>father</b>!<img src="https://images.dog.ceo/breeds/groenendael/n02105056_4600.jpg" width="200.000000"/></p>"
Documentation
See docs.md
for a full API reference.
Installing
Language | Install | |
---|---|---|
Python | pip install sqlite-html | |
Datasette | datasette install datasette-sqlite-html | |
Node.js | npm install sqlite-html | |
Deno | deno.land/x/sqlite_html | |
Ruby | gem install sqlite-html | |
Github Release |
The Releases page contains pre-built binaries for Linux amd64, MacOS amd64 (no arm), and Windows.
As a loadable extension
If you want to use sqlite-html
as a Runtime-loadable extension, Download the html0.dylib
(for MacOS), html0.so
(Linux), or html0.dll
(Windows) file from a release and load it into your SQLite environment.
Note: The
0
in the filename (html0.dylib
/html0.so
/html0.dll
) denotes the major version ofsqlite-html
. Currentlysqlite-html
is pre v1, so expect breaking changes in future versions.
For example, if you are using the SQLite CLI, you can load the library like so:
.load ./html0
select html_version();
-- v0.0.1
Or in Python, using the builtin sqlite3 module:
import sqlite3
con = sqlite3.connect(":memory:")
con.enable_load_extension(True)
con.load_extension("./html0")
print(con.execute("select html_version()").fetchone())
# ('v0.0.1',)
Or in Node.js using better-sqlite3:
const Database = require("better-sqlite3");
const db = new Database(":memory:");
db.loadExtension("./html0");
console.log(db.prepare("select html_version()").get());
// { 'html_version()': 'v0.0.1' }
Or with Datasette:
datasette data.db --load-extension ./html0
See also
- sqlite-http, for making HTTP requests in SQLite (pairs great with this tool)
- htmlq, for a similar but CLI-based HTML query tool using CSS selectors
- riyaz-ali/sqlite, the brilliant Go library that this library depends on
- nalgeon/sqlean, several pre-compiled handy SQLite functions, in C