31:[] 2f:{"title":"Introducing sqlite-html: query, parse, and generate HTML in SQLite","cells":[{"id":7,"value":"# Introducing sqlite-html: query, parse, and generate HTML in SQLite\n\n[sqlite-html](https://github.com/asg017/sqlite-html) is a new SQLite extension for querying, manipulating, and generating HTML. ","pinned":false,"hidden":false,"mode":"md"},{"id":16,"value":"At one point or another, you've probably needed to parse HTML: \nmaybe you were scraping your local government's COVID cases website, or extracting links in a news article, or searching for hidden elements in a page.","pinned":false,"hidden":false,"mode":"md"},{"id":0,"value":"\n`sqlite-html` is an alternative to other HTML parsers like [htmlq](https://github.com/mgdm/htmlq), [BeautifulSoup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/), and [Cheerio](https://cheerio.js.org/), with the added benefit of a full SQL API. Simply [load the extension](https://github.com/asg017/sqlite-html#as-a-loadable-extension) in your SQLite client of choice, and start parsing HTML with CSS selectors.","pinned":false,"hidden":false,"mode":"md"},{"id":214,"value":"```sql\n-- Using the SQLite CLI\n.load ./html0\n\nselect html_extract('

Anakin Skywalker

', 'b');\n-- \"Skywalker\"\n\nselect * from html_each('

', 'a', 'href'); \n-- './about'\n\n```","pinned":false,"hidden":false,"mode":"md"},{"id":219,"value":"sqlite-html can also _generate_ HTML with `html()` and `html_element()`, automatically escaping untrusted inputs.\n\n\n```sql\nselect html_element('img', json_object('src', './a.png', 'width', 200)); \n-- '

'\n\nselect html_element('p', null, \"text node\");\n-- '

text node

'\n\nselect html_element('p', null, \"Still a text node\"); \n-- '

<b>Still a text node</b>

'\n\nselect html_element('p', null, html('Trusted!')); \n-- '

Trusted!

` tags on the [California state goverment](https://www.ca.gov/) website.","pinned":false,"hidden":false,"mode":"md"},{"id":63,"value":"select\n html_attr_get(html, 'a', 'href') as href,\n text\nfrom html_each(\n (select page from pages where name = 'ca.gov'),\n 'a'\n)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":202,"value":"Now lets get all of today's top stories from [text.npr.org](https://text.npr.org/):","pinned":false,"hidden":false,"mode":"md"},{"id":155,"value":"select\n text as headline,\n printf(\n \"https://text.npr.org%s\",\n html_attr_get(html, 'a', 'href')\n ) as link\nfrom html_each(\n (select page from pages where name = 'text.npr.org'),\n 'a.topic-title'\n)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":239,"value":"Or all the external CSS files from [whitehouse.gov](https://www.whitehouse.gov):","pinned":false,"hidden":false,"mode":"md"},{"id":170,"value":"Inputs.table(\n db.sql`\nselect\n html_attr_get(html, 'link', 'href') as link\nfrom html_each(\n (select page from pages where name = 'whitehouse.gov'),\n 'link[rel=stylesheet]'\n)`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":187,"value":"Or all the election results ZIP files from [LA County's election results page](https://www.lavote.gov/home/voting-elections/current-elections/election-results/past-election-results).","pinned":false,"hidden":false,"mode":"md"},{"id":182,"value":"select\n html_text(items.html, \"strong\") as name,\n html_attr_get(\n html_extract(items.html, 'a[href*=\".zip\"]'),\n 'a',\n 'href'\n ) as href\n from html_each(\n (select page from pages where name = 'lacounty-results'),\n '.expandable_item.indent'\n) as items","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":244,"value":"Or let;s see all the embeded `