mind.* @blog.syslogd.net

Main menu

I apologize for the time it took before writing this part, but you know what they say: Better late then never.

In this part, I’ll explain how I extracted (scraped) the data from the Transports Quebec database mentioned in Part 1 using Python, Scrapy and a few other tools.

This post is not a full-fledged tutorial on using Scrapy, but it should give you a place to start if you’d like to do something similar.

In order to jump directly to the good stuff, I’ll skip the Scrapy installation and assume you are already familiar with Python. If you are not familiar with Python, you’re missing a lot. Get started here, then use some Google-Fu and start hacking on your own project to keep learning.

DISCLAIMER:

This small project was a quick and dirty drive-by experiment. This code is not: a complete solution, the best solution, production-ready, [insert your own similar statement here]. I’m sure you can do better 🙂 (Feel free to post your comments and improvements, however, keep in mind that I don’t plan to maintain or improve this code)

This code follows my late-night style guide.

For non-French speaking readers: This project extracts data from a French (Quebec, Canada) website, however, you can continue to read, the actual content is not really important and you should be able to follow without problem. You may also learn some French words. 😉

For French-speaking readers: If you are reading this, you probably won’t mind the English comments in my code. Unless required to do otherwise, I always write my code/comments in English. It’s easier to share this way.

I’m sure you’ll find typos.

Everything below was done using Python 2.7 and Scrapy v0.13. I have not tested with Scrapy 0.14+ or Python 3+ (UPDATE 2011/12/12: As PabloHoffman (none other than the lead Scrapy developer) noted in the comments, Scrapy does not support Python 3 and there are currently no plans to support it. Sorry about that!)

If you visit the website, you’ll notice that the first page you get is an empty form like this one:

So, to get to the actual data, you need to submit the form by clicking on “Afficher les résultats“. Luckily, the default form values will display all entries. After submitting the form, you’ll get results like these:

This is what our main scraper will have to deal with. Unfortunately, not all the fields we want to extract are available in this table. The rest is in what I call the “details” page. You get there by clicking on the “Fiche No” (first column) link (e.g. 00002). The details page look like this:

So for each entry found, our scraper will have to follow the “Fiche No” link and scrape the details page too.

In addition, each page of the report only contains 15 items. This means we’ll have to navigate the pages too.

NOTE: I used these tools. You may have your own tools that you prefer. Feel free to let me know of other good ones in the comments. (No need to point out that Safari has a web inspector, I know, but I did not find anything equivalent to Firefinder.)

Getting Started

Switch to a work directory then create your base Scrapy project (I called mine mtqinfra):

At this point we have a “skeleton” project. Now let’s create a very simple spider just to see if we can get this to work. (NOTE: There’s a “scrapy genspider” command but I won’t use it here.) Create the file spiders/mtqinfra_spider.py:

Now, the parse method return a “FormRequest” object that will instruct the bot to submit the form then call “self.parse_main_list” with the response.

$ scrapy crawl mtqinfra

Oops. The response contains no results. Whats wrong? Well, if you search a bit (I sniffed the network to compare what is sent by Firefox when the form button is pressed versus what is sent by Scrapy), you’ll find that the “p_request” form field is not set to “RECHR” by Scrapy as when sent by the browser. This is due to the fact that it is empty by default and set by a Javascript function. Let’s fix that:

Identifying HTML Elements and Their Corresponding XPath

Scrapy uses XPath to select and extract elements from a web page. Well, technically speaking you could parse the response body any way you want (e.g. using regular expressions), but XPath is very powerful so I suggest you give it a try.

I won’t write an XPath tutorial here, but simply put, XPath is a query language that allows you to select elements from HTML like you would do with SQL to extract fields from a table. Although XPath queries can appear intimidating at first, the XPath syntax itself is pretty simple.

Here are some tips to understand, learn and use XPath quickly and identify elements you want to extract.

Use Firebug to identify absolute XPath expressions

In Firebug, the absolute XPath expression to select an HTML element is displayed in a tooltip:

You can also right-click an element and select “Copy XPath” to copy it to the clipboard:

Use Firefinder to test XPath expressions

In Firebug, select the Firefinder tab and enter your XPath expression (or query or filter or selector, whatever you want to call it) then click “Filter”. The matching element(s) will be listed below and highlighted on the page. Because we’ll need to loop on each result table row, try it with this expression:

//table[@id="R10432126941777590"]//table[@summary="Report"]/tbody/tr

This expression will select each row of the result table.

Use “scrapy shell” to test XPath expressions in Scrapy

Scrapy has a very handy “shell” mode to help you test stuff. In order to bypass the form submission process and get directly on the result page, submit the form with your browser and then copy the URL that includes your session ID. If you do a “GET” on this URL, you’ll get the page you were viewing in your browser (as long as the session ID is still valid). Let’s try it:

IMPORTANT: Take note of lines 3 and 4. I am unsure why, but while Firefinder takes the “tbody” tag into account in XPath expressions, Scrapy does not want them. Thus, our previously working XPath returns nothing. If you remove the “tbody” tag (line 5), the expression will work and return the second row of the result table.

Line 8 shows the power of XPath and the Scrapy HtmlXPathSelector object. To extract an array of cells for row #2, on the HtmlXPathSelector for the row we simply call “select(‘td’)”.

The rest of the lines shows how to use the extract() method to extract HTML, text and attribute values.

Create XPath expressions that are general and specific at the same time

It is a very specific and absolute XPath to the results table. Should the web page change just a bit (e.g., an extra row in the first table of the form or a new table to hold new information), your XPath will become invalid or point to the wrong table. Now, in the page generated by the underlying reporting logic (Oracle Application Express (APEX) in this case), we noticed that the parent table of the results table has the “id” attribute set to “R10432126941777590” and that the actual results table has the attribute “summary” set to “Report”. We can then use the following XPath to get to the same table:

//table[@id="R10432126941777590"]//table[@summary="Report"]

It is more “general” as it skips over everything but two tables. It simply says: “Get me the tables that have their “summary” attribute set to “Report” that are also “under” (in) tables that have their “id” attribute set to “R10432126941777590”. However, because the “id” is very specific (only match one table) and the “summary” is also (somewhat) specific because it only match one table inside that other table, we are unlikely to match anything else. Thats what I mean by general and specific at the same time.

Now, I don’t know Oracle APEX enough to be certain the “id” used above won’t change if the report HTML format is modified, so maybe my solution could break later in this case, however, the principle in general is still good.

Use relative XPath expressions and HtmlXPathSelector objects

Don’t use absolute XPath expressions (as mentioned above) or repeat expressions in your code. Instead use the powerful HtmlXPathSelector objects to navigate in the HTML structure using relative XPath expressions. For example this code gets you columns 1-3 of row 2 or the results table but it sucks:

Why? Because in the first case, if anything changes in the HTML, you’ll need to modify 3 XPath expressions. In the second case, you’ll probably need to modify only one (if necessary at all). Of course, this example is simplified a bit to show you the concept (you’ll probably want to loop over rows and cells in your code as we’ll do later), but I hope you get the idea. Unfortunately, sometimes there is no (safe) way to get to an element other than by using an (almost) absolute XPath. Just try to minimize their use in your project.

The MTQInfraItem Object

The parsers that will handle responses as the website is crawled return “Item” objects (or Request objects to instruct the crawler to request more pages). “Item” objects are more or less a “model” of the data you will be scraping. It is a container for the structured data you will be extracting from the HTML pages. Edit the items.py file and replace the existing “MtqinfraItem” with this one:

Lines 2,105-108: We wrap our code in a try/except block to log any parsing error with our own message.Lines 11-13: This is where we skip the header. The logic works because the table header cells are “th” tags, not “td”, so cells is None.Lines 14-35: This is where we check if we’ve reached the last page or not. If not, we create the “Request” object for the next page.Lines 21-22: Note the commented “if last_record_on_page < 45:” line. We’ll refer to it in the “Testing It” section below.Lines 37-84: This is where we extract our data.Lines 86-101: Here, we create our MTQInfraItem and set the fields we just extracted.Line 102: Here we save our MTQInfraItem to our internal buffer so we can use it later when we parse the corresponding “details” page.Line 104: Finally we return a “Request” object so the crawler will request the corresponding “details” page and call our “parse_details” method with the response.

Scraping the Details

I won’t post the code to scrape the “details” page here as it is mostly code similar to lines 37-84 of the previous parser. The only thing to note is that in parse_details(), we actually return the final MTQInfraItem object to the crawler so it can be sent down the pipeline.

Testing It

Before you run this puppy for the first time, you should limit the crawling to a small number of records. I used 45 records because each page has 15. This give us a reasonable sample to validate most of our code. This is where line 22 in parse_main_list() comes handy. Simply uncomment it and comment line 23 to stop processing after 45 records.

If you try to run the crawler as-is on the Transports Quebec website, you’ll probably get errors. At least I did. Apparently, the website does not process concurrent requests using the same session ID. You get an error page when you attempt to do so. By default, Scrapy will attempt to crawl websites more quickly by executing requests concurrently. To disable this completely, add the following lines to settings.py:

CONCURRENT_REQUESTS_PER_DOMAIN = 1
CONCURRENT_SPIDERS = 1

REMEMBER: Be polite. Try to minimize the impact of your scraping on the web server. Do your testing on a small number of pages until your are satisfied with your output. Don’t scrape thousand of pages, add a new field and then scrape thousand of pages again. This is particularly true if you test this project. Don’t hammer the Transports Quebec website just for fun, they will simply raise my taxes to buy a bigger server 🙂

By default if you simply run “scrapy crawl mtqinfra” Scrapy will print each item on stdout. If you want to save the output in a usable format, you can use the “-o output_file” and “–output-format=format” options. e.g.:

scrapy crawl mtqinfra --output-format=csv -o output.csv

NOTE: If you attempt to save to XML at this point, you’ll only get a bunch of exceptions because the default XML exporter only handles strings fields and our items have floats. Read on for the solution.

Generating Output Files in Different Formats

OK, you tested the crawler and you are satisfied but you want to save the output in different formats, in a format of your own or in a database. This is where pipelines and exporters come into play.

A pipeline is simply a Python object with a “process_item” method. Once added to our settings.py file, the pipeline object will be instantiated by the crawler and its “process_item” method will be called for each MTQInfraItem. You can then save the item, change it or discard it so other pipelines won’t process it.

Exporters are objects with predefined methods that can be used to persist data in a specific format. Scrapy comes with predefined exporters for CSV, JSON, LineJSON, XML, Pickle (Python) and Pretty Print. You can easily subclass these to modify some of their behavior or subclass the BaseItemExporter class to create your own exporter. On our case, we’ll do both.

The MTQInfraXmlItemExporter and MTQInfraJsonItemExporter are simply customized versions of their equivalent base Scrapy exporters. The MTQInfraKmlItemExporter is a custom exporter to save output in KML format. It uses the simplekml module. Almost all the work is done in export_item(), which is the method called for each MTQInfraItem created by our parsers. start_exporting/finish_exporting are, as their name imply, called at the start and finish and can be used to setup your exporter or finalize the export process respectively.

Line 20 and other fields_to_export-related lines: This is used to export fields in a certain order and to exclude the fusion_marker field from all but the KML output.Lines 29-30: These lines connect Scrapy events to our pipeline. In this case, the spider_opened and spider_closed methods will be called on “start/stop” of the spider, allowing us to setup our exporters and call their start_exporting/finish_exporting methods.Lines 54-69: This method, as mentioned above, is called for each item created by our parsers. In turns, it calls the export_item method of each exporter.

In order for Scrapy to use our pipeline, we need to add the following lines to settings.py:

Running It For Real

When ready to run your scraper on thousand of pages, I suggest you add the following to settings.py:

LOG_FILE = 'mtqinfra.log'

Or use the –logfile option when running “scrapy crawl”. This will save the Scrapy output to the specified log file. If you still want to see things flowing on your terminal, do a “tail -f” on the log on another terminal, this way you get the best of both worlds.

As mentioned in “Testing It”, be polite. Try to make sure your code generate the proper output with a limited number of pages first. You don’t want to run your scraper for hours (this project does not take hours to crawl but this is an example) and then find out you forgot to include a field and need to reprocess each page.

Also, try to scrape the website during the night, when your traffic has probably less impact.

Finally, if you do run it and then realize your output has errors or needs to be changed, consider “reprocessing” your own results instead of scraping the website again (if possible). For this reason, I strongly suggest you always save your data in LineJSON format as it is super easy to reprocess. See next section for an example.

Reprocessing Results if Needed

If after scraping thousand of pages you realized you had a typo in a generated field (e.g. our KML popup), don’t rescrape the whole website again. Instead, consider reprocessing your own data. Of course, this can only be done if everything you need is already in your previously scraped data. If a field is missing completely and cannot be generated/computed, you’re out of luck.

Here’s an example of how you could reprocess previously saved LineJSON data:

###
### ADD NECESSARY MODULE IMPORTS AND/OR MODIFIED EXPORTERS/PIPELINES HERE
### SEE SAMPLE reprocess_json.py FILE IN GITHUB PROJECT FOR MORE DETAILS.
###
### Create a fake spider object with any fields/methods needed by your exporters.
class FakeSpider(object):
# Set spider name
# NOTE: Make sure you don't use the same one as the original spider because you'll
# overwrite the previous data (and with this implementation, script will fail too).
name = "mtqinfra-reprocessed"
### MAIN
# This is the previously scraped data
input_file = open("mtqinfra.linejson")
pipeline = MTQInfraPipeline()
pipeline.spider_opened(FakeSpider)
for line in input_file:
item = MTQInfraItem(json.loads(line))
pipeline.process_item(item, FakeSpider)
pipeline.spider_closed(FakeSpider)
input_file.close()

The Source Code

Final Note

Scrapy is a very powerful scraping framework. It does much more than what I use in this project. Have a look at the documentation to learn more.

I’ll stop here as writing this post actually took more time than coding the project itself. Yes, I’m serious. This either shows you how powerful Python+Scrapy are, or how much I suck at writing blog posts 🙂

I hope someone will find this useful. Feel free to share in the comments section.

I had a few issues with Tumblr (including the “Our servers are too busy” kind) and limited support for code blocks and decided to switch platforms.

At least with this one, if at a later time I can’t handle the limitations of being hosted in a shared/managed environment, I’ll be able to export everything and re-import in my own instance of WordPress easily.

Not long ago, @mtlouvert tweeted about this Transports Quebec database saying it would be nice to have access to all the data in a usable format. I thought it would be a nice project but was busy working on other things.

Sunday night I had some spare time so I decided to check what could be done. I did not find any way to completely download the data by downloading reports etc… So I turned to Python and the fantastic scraping framework Scrapy.

A few hours later I had a working crawler and a bit later output in different formats. I will post something more technical about the process soon.

Here are the results:

This morning, big surprise, I found on Twitter that James McKinney has done a similar exercise last night. I guess Sunday night is hacking night for him too. I checked his data and realized that not all the database was available. So I thought I’d release my stuff anyway. Others may find it useful.

Today at lunch and later in the afternoon I finalized a few things. I wanted to display the KML on a map but the generated KMZ is too large to display on a Google Maps directly so I ended up using a Google Fusion Table.

Here are links to the Fusion table and the data in different format:

Google Fusion Table (Click on Visualize then Map to get the map. Zoom in a bit to click on actual markers and see the description)

UPDATE 2011/11/10: I’ve noticed a missing double-quote in the KML description field that caused two rows to be “merged” so I have regenerated the data, created a new fusion table with it and updated the links. I also removed the “fusion_marker” field from the non-KML format as it was added specifically for the fusion table.