Querying and Analyzing Data in SQLite

From Pages to Evidence

A collection of web pages becomes useful for analysis only after the crawler has recorded both the pages and the connections between them. The spider.py program performs that collection: it starts from a URL, fetches a page, extracts links, stores the page and its outgoing links in spider.sqlite, and continues through unvisited links. The resulting database is not just a list of URLs. It is a graph of pages and relationships that can later be queried, analyzed, and visualized.

The Crawl Decision Loop

Each crawl follows a repeating decision pattern. The program begins with a starting URL, visits a page, finds that page's outgoing links, stores the page and its relationships, and then selects another unvisited link. This systematic loop changes a live web of pages into a stored snapshot of the portion that has been collected.

From Live Content to SQLite Records

The crawler writes to a persistent SQLite file named spider.sqlite. The database contains at least two tables: one for pages and one for links. A page record includes a unique ID, the URL, and the count of outgoing links. A link record stores a source page ID and a target page ID. Together, these records preserve both the page itself and the direction of each connection.

This structure supports questions about direction. You can find the pages that a given page links to by following relationships from its source ID. You can also find pages that link to a given page by looking for relationships with that page as the target ID. The same stored relationships can support network statistics and later visualization.

Incremental Crawling

The database is designed to grow across sessions. When spider.py runs again, it remembers which pages are already stored and skips them. If one session collects 10 pages and a later session requests 20 pages, the later session fetches only the 10 new pages needed to extend the collection. The existing records remain in spider.sqlite, so the second session builds on the first instead of starting over.

Separate Webs, Shared Database

A single database can contain pages discovered from multiple independent starting URLs. For example, one session can begin at http://www.dr-chuck.com/ and another can begin at http://www.wikipedia.org/. The two collections are called separate webs within the program, but their pages and links are stored in the same spider.sqlite file.

The crawler does not maintain a separate visit queue for each starting point. It treats all unvisited links across the webs as one unified collection and randomly selects from that collection. As a result, later visits can interleave pages from the different starting points. The webs may eventually connect through links, or they may remain separate clusters or isolated portions of the stored graph.

Reading the Stored Graph

Once crawling has produced records, querying becomes a way to inspect the graph. Page records can answer questions about URLs and outgoing-link counts. Link records can answer which pages a page points to and which pages point back to it. These are different directions, so analysis should make clear whether it is following a source page outward or searching for incoming links to a target page.

The raw records can also feed later analysis and visualization. Algorithms such as a simplified page rank calculation can use the graph to estimate which pages are central or influential within the collected web. D3 can read the page and link data and render pages as nodes and links as edges, making clusters, hub pages, and isolated pages easier to see.

Mistakes in Crawl Analysis

• Treating the database as only a list of URLs.

  The connections between pages are stored separately as relationships containing source and target page IDs.

  Fix: Use both page records and link records when analyzing connectivity.

• Assuming a later crawl starts from an empty database.

  The crawler skips pages already in the database and continues with unvisited links.

  Fix: Treat later sessions as incremental growth unless spider.sqlite has been deleted.

• Keeping separate queues for separate starting URLs.

  All unvisited links across the webs are treated as one unified collection.

  Fix: Expect the crawler to select randomly from unvisited links across the stored webs.

• Confusing outgoing and incoming relationships.

  An outgoing count describes links from the page, while incoming links must be found by examining target relationships.

  Fix: Identify whether the analysis starts from a source page or searches for a target page.

Practice the Trace

Key Takeaways

1. spider.py turns live pages and their outgoing links into a persistent graph stored in spider.sqlite.
2. The page table records page IDs, URLs, and outgoing-link counts; the link table records source and target page IDs.
3. Repeated sessions extend the database by skipping pages already stored and selecting unvisited links.
4. Multiple starting URLs can create separate webs that share one database and one unified collection of unvisited links.
5. Queries, graph algorithms, and D3 visualizations can transform the stored records into connectivity insights.