Overview

version 0.1

Two simple classes for capturing a database. This is 'fit for purpose' for the project, and may not work perfectly for your needs. I recommend running a trial pass on the current state of the database to confirm that data is being captured - see details in the Pilot scrape section below.

Involves very little in the way of error/exception checking, and will need to be restarted at various points. This could readily be done in a single class, but I hate fiddling with SQL and just wanted the scraper in place before developing the SQL handler.

During a test of the degree of effective data capture, published data counts (n=3,868,610 ±10) to CACL-captured data counts (n=3,858,468) at time of completion indicates a capture rate above 99.7%. This does not reflect the data entry errors and inconsistencies in the official data, which this tool is not designed to address.

class CACLManager

• Captures data and stores it locally in .json files.
• This will need to be periodically restarted to capture data, and will likely take several days.
• The default delay between http requests is 0.1 seconds, on top of the processing time. This is necessary to avoid stalls, but could be adjusted up or down by modifying the time.sleep(0.1) instruction in the start method.

class CACLDirLibrary 

• Captures the .json files and inserts into an SQL file for analysis.
• Once the CACLManager has concluded, the json can be imported into a library.

Instructions

I recommend running this through a jupyter notebook for convenience.

1. Class objects

First, instantiate the class objects:

manager = CACL.CACLManager()
library = CACL.CACLDirLibrary()

1a. set UserAgent (optional)

A custom UserAgent can be set by providing a class called UserAgent that will spit out a formatted user agent string in response to a get_UA() method. A crude equivalent is flubbed at the start of the library so that if this is missing then a User Agent is still provided, which can be renamed to your relevant project. This can also be done by changing the ua.ua string after the manager is instantiated:

manager.ua.ua = <valid user agent string>

1b. Pilot scrape (optional, recommended)

I recommend first running a pilot scrape to ensure that html tags are still relevant. At time of writing, the ACB is going through a classification review, that includes a goal of database modernisation, so outputs should be tested. 

A pilot scrape method is provided, which can be run as:

result = manager.pilot_scrape()

This creates a dictionary, result, which has two keys which contain dictionaries that provide a snapshot of what the tool can capture at the point you run the pilot scrape.

• "dir_page", a dictionary that includes:
  • "response": a review of the HTTP response;
  • "soup": the HTML as a BeautifulSoup object that appears as it will to the class.
  • and "ok": a boolean that assesses whether a valid page has been returned - which is probably the most important value for determining the success of the tool.
• "parse", a dictionary that includes all the meaningful data points that the tool has captured out of the soup object.
  • The exact keys of this dictionary will vary depending on what data is found.
  • This itself varies due to inconsistencies in the database, and older entries simply lack some variables.

2. Scraping data

Import the CACL module, then initiate the scrape using an instance of CACLManager via:

manager.start() 

Note: this will take several days. The terminal should provide an indication of when the manager has stalled. You can safely assume that if the process has stalled for more than 20 seconds then the process should be manually halted and restarted. The page iteration is the last thing to happen, and keyboard interrupts (i.e. ctrl-C) are handled semi-softly, and as such iterations are unlikely to be skipped through this process.

3. Store data

Once the scrape is concluded, the CACLDirLibrary class can be used to manage the database. This will iterate over the .json files and store them in an sqlite database that defaults to  "library.db". This database can be given a different name by passing a valid filename string to the library_dir attribute of the class:

library.library_dir = "<filename>.db" 

Processing the data into the database simply requires 

library.start() 

This should take about 30 seconds, depending on your system.

4. SQL data access

Once the CACLDirLibrary is completed, SQL data can then be accessed via R/R studio/DBeaver or similar.
Generally datasets are far too large for processing via excel or similar, so I do not recommend attempting this.

library.cursor(<sql query>)
results = library.cursor.fetchall()

Example searches:

library.cursor.execute("SELECT date, title, image, icon, category_detail FROM dir_works WHERE image='Computer Games';")

The image variable contains the medium of the classification, so this can be passed an alternative valid string (such as 'films') to capture information on other media than computer games. 

5. Exporting as spreadsheet (optional)

If you're not sure about what tool to use to analyse the data, results can be exported in various formats, following the various pandas library export functions. In the example below, data is grouped in order to ensure the output does not exceed the table length of Excel, and then exports as an .xlsx

library.cursor.execute("SELECT substr(date, 1, 7) as new_date, icon, category_detail, COUNT(*) FROM dir_works WHERE image='Computer Games' GROUP BY new_date, category_detail, icon;")
computer_games_DF = pd.DataFrame(library.cursor.fetchall()) 
computer_games_DF.to_excel("games.xlsx")

Role of libraries in requirements.txt

bs4

• i.e. BeautifulSoup, used for parsing HTML

requests

• used for managing http requests

sqlite3

• used for creating the SQL database
• some distributors bundle this anyway

pandas

• for post-scrape data management