3e:null 3f:["$","title",null,{"children":"Introducing sqlite-html: query, parse, and generate HTML in SQLite | Alex Garcia | Observable"}] 45:[] 43:{"id":"08d38942152efec7","slug":"introducing-sqlite-html","title":"Introducing sqlite-html: query, parse, and generate HTML in SQLite","thumbnail":"0c41c8d3e87484ae075e182e3d5a592873b058b3f0f2f7c3d558dfcb31183043","access_level":"public","create_time":"$D2022-01-03T01:51:05.383Z","update_time":"$D2022-09-13T01:30:37.406Z","view_time":null,"first_public_time":"$D2022-08-02T22:01:03.283Z","has_unpublished_edits":null,"fork":false,"stars":10,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024","bio":"Software engineer, available for freelance work! • he/him/his"},"version":263,"publish_version":263,"role":null,"authored":false,"authors":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"bio":"Software engineer, available for freelance work! • he/him/his"}],"members":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"ec34b19c7b7564c5","slug":"sqlite-blog","title":"SQLite Blog","owner":{"id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia"}},"type":"notebook","body":{"titling":"auto","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:db"},{"id":244,"value":"Or let;s see all the embeded `