31:T517,First up: simple JSON endpoints. Let's try to get the population count for every county in California using the [US Census Bureau's API](https://www.census.gov/data/developers/data-sets.html). ```sql select value ->> '$[2]' as county, cast(value ->> '$[0]' as integer) as population from json_each( http_get_body('https://api.census.gov/data/2020/acs/acs5?get=B01001_001E&for=county:*&in=state:06') ) where key != 0 -- 1st element is headers, ex. '["B01001_001E","state","county"]' order by population desc limit 6; /* ┌────────┬────────────┐ │ county │ population │ ├────────┼────────────┤ │ 037 │ 10040682 │ │ 073 │ 3323970 │ │ 059 │ 3170345 │ │ 065 │ 2437864 │ │ 071 │ 2162532 │ │ 085 │ 1924379 │ └────────┴────────────┘ */ ``` Pretty slick! With [SQLite's builtin JSON support](https://www.sqlite.org/json1.html), we can use the "`->>`" syntax to extract specific values we're interested in. The "county" columns are [FIPS code](https://en.wikipedia.org/wiki/Federal_Information_Processing_Standards) of the California counties (037=Los Angeles, 073=San Diego county, etc.) 32:T852,select json_extract(value, '$.BlockFareboxId') as block_farebox_id, json_extract(value, '$.CommStatus') as comm_status, json_extract(value, '$.Destination') as destination, json_extract(value, '$.Deviation') as deviation, json_extract(value, '$.Direction') as direction, json_extract(value, '$.DirectionLong') as direction_long, json_extract(value, '$.DisplayStatus') as display_status, json_extract(value, '$.StopId') as stop_id, json_extract(value, '$.CurrentStatus') as current_status, json_extract(value, '$.DriverName') as driver_name, json_extract(value, '$.GPSStatus') as gps_status, json_extract(value, '$.Heading') as heading, json_extract(value, '$.LastStop') as last_stop, json_extract(value, '$.LastUpdated') as last_updated, json_extract(value, '$.Latitude') as latitude, json_extract(value, '$.Longitude') as longitude, json_extract(value, '$.Name') as name, json_extract(value, '$.OccupancyStatus') as occupancy_status, json_extract(value, '$.OnBoard') as on_board, json_extract(value, '$.OpStatus') as op_status, json_extract(value, '$.RouteId') as route_id, json_extract(value, '$.RunId') as run_id, json_extract(value, '$.Speed') as speed, json_extract(value, '$.TripId') as trip_id, json_extract(value, '$.VehicleId') as vehicle_id, json_extract(value, '$.SeatingCapacity') as seating_capacity, json_extract(value, '$.TotalCapacity') as total_capacity, json_extract(value, '$.PropertyName') as property_name, json_extract(value, '$.OccupancyStatusReportLabel') as occupancy_status_report_label from snapshots join json_each(snapshots.response_body, '$.Vehicles')33:T435,```sql create table failed_banks as select -- Extract the "Bank Name" value from the row html_trim( html_text(html_table(rows.html), 'td:nth-child(1)') ) as bank_name, -- Format the link found inside the first field to a direct link printf( 'https://www.fdic.gov%s', html_attr_get(html_table(rows.html), 'td:nth-child(1) a', 'href') ) as link, html_text(html_table(rows.html), 'td:nth-child(2)') as city, html_text(html_table(rows.html), 'td:nth-child(3)') as state, html_text(html_table(rows.html), 'td:nth-child(4)') as cert, html_text(html_table(rows.html), 'td:nth-child(5)') as acquiring_institution, html_text(html_table(rows.html), 'td:nth-child(6)') as closing_date, html_text(html_table(rows.html), 'td:nth-child(7)') as find -- Go through all rows inside the first ".dataTable" element from html_each( -- Get the HTML page in memory http_get_body( 'https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/' ), '.dataTable tbody tr' ) as rows; ```34:T9f3,## ZIP Files One last party trick: SQLite has support for [querying and generating ZIP files](https://sqlite.org/zipfile.html). There aren't too many ZIP APIs out there, but one of particular interesting note are Github repositories. For example, let's take a look at [oliverbenns/john-carmack-plan](https://github.com/oliverbenns/john-carmack-plan), which is an archived dataset described as: > *"An archive of John Carmack’s .plan files in readable Markdown format that documents the development and production of Id Software's games from 1996 to 2010, with mentions of the company's most popular games Commander Keen, Wolfenstein 3d, Doom and Quake."* - Oliver Benns Digging through the repo, the `archive/` folder contains 1 markdown file per day, 300+ files. Using the [Github API](https://docs.github.com/en/rest/repos/contents) won't be useful since that's 300+ separate requests and would take forever, but we can instead download the whole repo as a ZIP files and parse that! ```sql select name, length(data) from zipfile( http_get_body('https://github.com/oliverbenns/john-carmack-plan/archive/refs/heads/master.zip') ) limit 10; /* ┌────────────────────────────────────────────────┬──────────────┐ │ name │ length(data) │ ├────────────────────────────────────────────────┼──────────────┤ │ john-carmack-plan-master/ │ │ │ john-carmack-plan-master/README.md │ 525 │ │ john-carmack-plan-master/archive/ │ │ │ john-carmack-plan-master/archive/1996-02-18.md │ 453 │ │ john-carmack-plan-master/archive/1996-02-19.md │ 530 │ │ john-carmack-plan-master/archive/1996-02-20.md │ 525 │ │ john-carmack-plan-master/archive/1996-02-21.md │ 989 │ │ john-carmack-plan-master/archive/1996-02-22.md │ 482 │ │ john-carmack-plan-master/archive/1996-02-23.md │ 600 │ │ john-carmack-plan-master/archive/1996-02-24.md │ 529 │ └────────────────────────────────────────────────┴──────────────┘ */ ``` 35:T1026,```sql select * from carmack_plans limit 2; /* ┌────────────┬───────────────────────────────────────────────────┐ │ date │ contents │ ├────────────┼───────────────────────────────────────────────────┤ │ 1996-02-18 │ * page flip crap │ │ │ * stretch console │ │ │ * faster swimming speed │ │ │ * damage direction protocol │ │ │ * armor color flash │ │ │ * gib death │ │ │ * grenade tweaking │ │ │ * brightened alias models │ │ │ * nail gun lag │ │ │ * dedicated server quit at game end │ │ │ │ │ │ + scoreboard │ │ │ + optional full size │ │ │ + view centering key │ │ │ + vid mode 15 crap │ │ │ + change ammo box on sbar │ │ │ + allow "restart" after a program error │ │ │ + respawn blood trail? │ │ │ + -1 ammo value on rockets │ │ │ + light up characters │ │ │ │ │ │ vsync on high framerates │ ├────────────┼───────────────────────────────────────────────────┤ │ 1996-02-19 │ * fixed vanished bmodels at startup │ │ │ * fixed getting stuck problem │ │ │ * fixed the sound out of range problem │ │ │ * hack patched the dissapearing under water │ │ │ │ │ │ + remove look spring │ │ │ + fix radius damage │ │ │ + too many people on status bar │ │ │ + invis in water │ │ │ + multiple pain sounds started │ │ │ + shift jumps while in console │ │ │ + less blood time │ │ │ + gone armor icon │ │ │ + look up/down keyboard modifier │ │ │ │ │ │ clamp velocity on respawn │ │ │ clear damage flash on respawn │ │ │ bad changeup │ │ │ bob speed kept on restart │ │ │ no puffs in sky volumes │ │ │ id copyright seal │ │ │ teleport screen warp │ └────────────┴───────────────────────────────────────────────────┘ */ ```36:T890,## The not-so-great parts A few drawbacks to using sqlite-http specifically: - You can't download a response body larger than 1GB (a [SQLite limitation](https://www.sqlite.org/limits.html#max_length)) - No retry support yet - No proxy support yet Some of these may be fixed in a future version of sqlite-http, but taking the library as-is today, these limitations might come up in more complex web-scraping tasks. Another serious limitation: CSVs. Querying CSVs in SQLite is an awkward problem in general. You either had to run the weird [`.import`](https://sqlite.org/cli.html#importing_files_as_csv_or_other_formats) command in the SQLite CLI, compile the official [CSV Virtual Table extension](https://www.sqlite.org/csv.html) extension and load it yourself, or download the slightly-modified [vsv](https://github.com/nalgeon/sqlean/blob/main/docs/vsv.md) version from the sqlean project. But all of these require the CSV to be on the filesystem, you can't use SQL expression like `http_get_body()` to load in data. You could hack around this with the SQLite CLI [`writefile()`](https://www.sqlite.org/cli.html#file_i_o_functions) function, by writing the CSV to a temporary file like so: ```sql select writefile( 'tmp.csv', http_get_body('https://raw.githubusercontent.com/jasonong/List-of-US-States/master/states.csv') ); .mode csv .import tmp.csv us_states select * from us_states limit 5; /* ┌────────────┬──────────────┐ │ State │ Abbreviation │ ├────────────┼──────────────┤ │ Alabama │ AL │ │ Alaska │ AK │ │ Arizona │ AZ │ │ Arkansas │ AR │ │ California │ CA │ └────────────┴──────────────┘ */ ``` But, you would need to delete the temporary file at some point, and you're still limited by 1GB files. In the future, I plan to release a new SQLite CSV extension that's flexible enough to use with sqlite-http and other tools, but for now, scraping CSV's isn't pleasant in pure SQLite.37:[{"id":"c5ba1a497715192a3ccd8e90e426c0b3b648f2fa7fc3206908960dbd38dd3e385b2776a83c8de9a33a874e98976992da93c447355710534d6254d1144d2b8912","href":"https://static.observableusercontent.com/files/c5ba1a497715192a3ccd8e90e426c0b3b648f2fa7fc3206908960dbd38dd3e385b2776a83c8de9a33a874e98976992da93c447355710534d6254d1144d2b8912","name":"DALL·E 2022-08-19 11.41.06 - cute cartoon abominable snowman scraping ice off his frozen car windsheild.png","type":"image/png","lastModified":1660934536991.344,"size":1378708},{"id":"ed2b2c35d77f6e48e54284cfaf8526eaf21b4bd3c2c2a3171294e70cc1aa41a01f1b284cc4ee361bef715ba972e74521310874f93ca80005d1d88d6944973fd8","href":"https://static.observableusercontent.com/files/ed2b2c35d77f6e48e54284cfaf8526eaf21b4bd3c2c2a3171294e70cc1aa41a01f1b284cc4ee361bef715ba972e74521310874f93ca80005d1d88d6944973fd8","name":"DALL·E 2022-08-19 11.41.11 - cute cartoon abominable snowman scraping ice off his frozen car windsheild.png","type":"image/png","lastModified":1660934538182.345,"size":1397945},{"id":"758457c485e7104e0df4498192b67fc95dd03d14a69404776c7bd79c212a3f1bd0354aa137b7bf73a53203b86e04d61edf54c2551208bd0c2375f33f17c2d4cf","href":"https://static.observableusercontent.com/files/758457c485e7104e0df4498192b67fc95dd03d14a69404776c7bd79c212a3f1bd0354aa137b7bf73a53203b86e04d61edf54c2551208bd0c2375f33f17c2d4cf","name":"DALL·E 2022-08-19 11.41.16 - cute claymation abominable snowman scraping ice off his frozen car windshield.png","type":"image/png","lastModified":1660934540134.778,"size":1736321}] 2f:{"title":"Scraping JSON, HTML, and ZIP Files with Pure SQLite","cells":[{"id":407,"value":"# Scraping JSON, HTML, and ZIP Files with Pure SQLite\n\na.k.a. leave BeautifulSoup in the past and embrace SQL","pinned":false,"hidden":false,"mode":"md"},{"id":409,"value":"\n ${pics.map(d=>htl.html`
`)}\n
\n ","pinned":false,"hidden":false,"mode":"html"},{"id":453,"value":"*I used DALL·E to generate thumbnails for this post: “cute cartoon|claymation abominable snowman scraping ice off his frozen car windshield” is nightmare fuel*","pinned":false,"hidden":false,"mode":"md"},{"id":0,"value":"\nSome of the most common web-scraping tasks can be done in pure SQLite - meaning no Python, Node, Ruby, or other programming languages necessary, only the SQLite CLI and some extensions.\n\nThe main extension that enables this: [sqlite-http](https://github.com/asg017/sqlite-http), which allows you to make HTTP requests and save responses entirely in SQLite. Check out the \"[Introducing sqlite-http](https://observablehq.com/@asg017/introducing-sqlite-http)\" post to learn more. \n\nLet's jump into some real examples!","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"## Scraping JSON APIs","pinned":false,"hidden":false,"mode":"md"},{"id":15,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":44,"value":"Now, let's take a look an a more unofficial source: one of my local bus lines, the [Montebello Bus Tracker](http://www.ridembl.com:8080/MBLBusTracker/BusTracker/Index). Digging around in those network requests, I see that this URL is JSON of one of the local routes: \n\nhttp://www.ridembl.com:8080/MBLBusTracker/BusTracker/getRouteDetailByRouteID?RouteId=10\n\nSo let's scrape it! Say we want to scrape the vehicle locations from that endpoint every five minutes. We'll use `http_get` to retrieve all info from our requests: the JSON body, the HTTP headers, status codes, timing information, everything.\n\nLet's create a `snapshots` table with the same schema as all the columns from `http_get()`.\n\n```sql\ncreate table if not exists snapshots as\n select * from http_get() limit 0;\n```\n\nNow, scraping that endpoint is as easy as:\n\n```sql\ninsert into snapshots\n select *\n from http_get('http://www.ridembl.com:8080/MBLBusTracker/BusTracker/getRouteDetailByRouteID?RouteId=10');\n```\n\nAnd the resulting table looks like this:","pinned":false,"hidden":false,"mode":"md"},{"id":154,"value":"select * from snapshots;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":68,"value":"Now the real juice is inside the `response_body` column, that's the JSON with all the info we care about. \n\nSpecifically, looking inside that large JSON object, I want what's inside the `\"Vehicles\"` key, which is an array of each bus running on the specified route, include latitude/longitude, the stop it's going to, and current seating capacity. \n\nWith [`json_each`](https://www.sqlite.org/json1.html#jeach) and some elbow grease, we can extract those into a new table!","pinned":false,"hidden":false,"mode":"md"},{"id":196,"value":"select \n snapshots.rowid, \n vehicles.value \nfrom snapshots \njoin json_each(snapshots.response_body, '$.Vehicles') as vehicles","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":73,"value":"Now we can extract specific columns inside those objects. This can be cumbersome to write manually, but we can instead copy a sample object and paste it into this [JSON to SQLite helper](https://observablehq.com/@asg017/json-to-sqlite-helper) to auto-generate the SQL code for these columns! Here's the result of that:","pinned":false,"hidden":false,"mode":"md"},{"id":163,"value":"$32","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":79,"value":"And there we have it! You can throw the `insert into snapshots...` snippet into a cron job, make a new `vehicle_readings` table with a trigger on every new snapshots insert, and you have yourself a scheduled web scraper with a built-in audit log of every single HTTP request and server response.","pinned":false,"hidden":false,"mode":"md"},{"id":7,"value":"## Scraping HTML","pinned":false,"hidden":false,"mode":"md"},{"id":18,"value":"The [sqlite-html](https://github.com/asg017/sqlite-html) extension allows for querying HTML with CSS selectors. Combined it with `sqlite-http`, and we have an easy-to-use HTML scraping framework!","pinned":false,"hidden":false,"mode":"md"},{"id":13,"value":"Let's scrape the FDIC [List of failed banks](https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/) into a SQLite table. It's small enough to all fit in memory, so let's do it all in one go!","pinned":false,"hidden":false,"mode":"md"},{"id":119,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":170,"value":"select * from failed_banks;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":130,"value":"Needing to specify exact columns can be annoying. However, it can be a nice exercise to dig through all the field in a HTML document. For example, extracting the `href` attribute inside the `` element for the `link` column is something most automated HTML readers wouldn't do, and \"hidden\" fields like that can be important to track and capture!","pinned":false,"hidden":false,"mode":"md"},{"id":20,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":134,"value":"Sweet, looks like iterating over each file in the ZIP should be pretty easy!\n\nLet's parse out the YYYY-MM-DD timestamp from that long filename, and save the text contents into a new table.","pinned":false,"hidden":false,"mode":"md"},{"id":95,"value":"```sql\n.bail on\n.load dist/http0\n\ncreate table carmack_plans as\n select\n -- \"john-carmack-plan-master/archive/1996-02-18.md\" -> \"1996-02-18\"\n substr(\n name, \n length(\"john-carmack-plan-master/archive/\") + 1, \n length(\"YYYY-MM-DD\")\n ) as date,\n cast(data as text) as contents\n from zipfile(\n http_get_body('https://github.com/oliverbenns/john-carmack-plan/archive/refs/heads/master.zip')\n )\n where name like \"%archive/%.md\";\n```","pinned":false,"hidden":false,"mode":"md"},{"id":105,"value":"$35","pinned":false,"hidden":false,"mode":"md"},{"id":136,"value":"And now let's use SQLite builtin [full text search extension](https://www.sqlite.org/fts5.html) to build a search engine for these plans!\n","pinned":false,"hidden":false,"mode":"md"},{"id":99,"value":"```sql\ncreate virtual table fts_carmack_plans using fts5(contents);\n\ninsert into fts_carmack_plans(rowid, contents)\n select rowid, contents from carmack_plans;\n```","pinned":false,"hidden":false,"mode":"md"},{"id":353,"value":"Let's test it out by searching for all mentions of \"teleport\":","pinned":false,"hidden":false,"mode":"md"},{"id":356,"value":"select \n carmack_plans.*, \n rank\nfrom fts_carmack_plans('teleport')\nleft join carmack_plans on carmack_plans.rowid = fts_carmack_plans.rowid\norder by rank","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":367,"value":"Now let's hookup a search field using Observable's [Inputs](https://github.com/observablehq/inputs), and a selectable table of results to read the full file! ","pinned":false,"hidden":false,"mode":"md"},{"id":176,"value":"viewof query = Inputs.text({ value: \"explode\", label: \"query\", submit: true })","pinned":false,"hidden":false,"mode":"ojs"},{"id":242,"value":"viewof selected = Inputs.table(matches, { multiple: false, value: matches[0] })","pinned":false,"hidden":false,"mode":"ojs"},{"id":336,"value":"Preview: John Carmack's .plan file on ${selected.date}\n
\n ${selected.contents}\n
","pinned":false,"hidden":false,"mode":"html"},{"id":228,"value":"matches = db.sql`select \n carmack_plans.date,\n carmack_plans.contents,\n snippet(fts_carmack_plans, 0, '', '', '...', 20) as snippet,\n rank\nfrom fts_carmack_plans(${query})\nleft join carmack_plans on carmack_plans.rowid = fts_carmack_plans.rowid\norder by rank`","pinned":false,"hidden":false,"mode":"ojs"},{"id":376,"value":"## Why SQLite + sqlite-http is a good scraping option\n\nOnce you have a SQL query that scrapes the data you need and transforms it into something useful, saving is as easy as adding \"create table xxx as \" or \"insert into xxx\" before your query. You'll instantly get a table of data in your SQLite database that's ready for further analysis, and it's all saved into a file!\n","pinned":false,"hidden":false,"mode":"md"},{"id":378,"value":"\n```sql\n-- initialize the table\ncreate table github_events as\n select value\n from json_each(http_get_body('https://api.github.com/events'));\n\n-- insert into it as much as you want!\ninsert into github_events\n select value\n from json_each(http_get_body('https://api.github.com/events'));\n\n-- do some analysis on your new table!\nselect \n value ->> '$.type', count(*) \nfrom github_events \norder by 2 desc;\n/*\n┌────────────────────┬──────────┐\n│ value ->> '$.type' │ count(*) │\n├────────────────────┼──────────┤\n│ PushEvent │ 180 │\n└────────────────────┴──────────┘\n*/\n```\n\n","pinned":false,"hidden":false,"mode":"md"},{"id":399,"value":"\nWhat if you're scraping multiple sources? No problem, create multiple tables for each source, then add data processing steps that JOINs tables as needed to clean up your data.","pinned":false,"hidden":false,"mode":"md"},{"id":292,"value":"\n\nThe [`http_get()`](https://github.com/asg017/sqlite-http/blob/main/docs.md#request-everything) and related table functions are extremely useful when working on scheduled scrapers, as well. You may have a scraper that downloads data from an endpoint like so:\n\n```sql\n-- \"limit 0\" just to get the right schema to create an empty table\ncreate table snapshots as \n select * from http_get() limit 0;\n\ninsert into snapshots\n select * from http_get(\"https://website.youre.scraping/\")\n```\n\nOut of the 13 columns returned by `http_get()`, you probably only care about `response_body` most of the time. But when errors occur, then `response_status_code` and `response_headers` are nice to have in an audit log. `remote_address` can help you track IP addresses of responding servers, and `timings` let you track how long TLS handshakes, connections, DNS lookups, and response body readings take. All without needing to write a custom Python or Node script.\n","pinned":false,"hidden":false,"mode":"md"},{"id":380,"value":"\nAnother advantage: **SQLite is extremely small and can be easy to manage**. On my Mac, my SQLite CLI binary is `~1.4MB`, and sqlite-http sits at ~8MB (much larger since it's written in Go). \n\nIn comparison, my Node binary is 83MB (not including any 3rd party libraries), Deno is 81MB, and Python is too complex to even count (online tells me between 25MB and 120MB???). Downloading the CLI and managing individual extension files can be awkward at first, but I personally much prefer it to larger and more complex tools. ","pinned":false,"hidden":false,"mode":"md"},{"id":277,"value":"$36","pinned":false,"hidden":false,"mode":"md"},{"id":385,"value":"## Stay in the loop!\n\nThis is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) email a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me).\n\nFuture topics: Building SQLite extensions in Go, new SQLite extensions for parsing URLs and paths","pinned":false,"hidden":false,"mode":"md"},{"id":223,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":144,"value":"import { DatasetteClient } from \"@ambassadors/datasette-client\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":151,"value":"db = new DatasetteClient(\"https://sqlite-extension-examples.fly.dev/data\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":430,"value":"pics = [\n await FileAttachment(\n \"DALL·E 2022-08-19 11.41.06 - cute cartoon abominable snowman scraping ice off his frozen car windsheild.png\"\n ).url(),\n await FileAttachment(\n \"DALL·E 2022-08-19 11.41.11 - cute cartoon abominable snowman scraping ice off his frozen car windsheild.png\"\n ).url(),\n await FileAttachment(\n \"DALL·E 2022-08-19 11.41.16 - cute claymation abominable snowman scraping ice off his frozen car windshield.png\"\n ).url()\n]","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W37","stdlib":"1","titling":"auto"}