(click
for larger image)
last update: 03/06/2004
last update: 04/04/2003
anthracite examples
MySQL Export : Stock Quotes
The MySQL Export Object makes it possible to insert results from Anthracite processes into MySQL databases.
This document image shows a set of process chains that take input from two different sources, extract three different pieces of data from the documents, and then insert the data into a MySQL database.
At the top, the two source objects represent two different versions of the same basic page, a stock quote page from Yahoo which I use to keep track of the market. In this case, one of the sources loads a stock quote page for the Dow Jones Industrial Average and the other a stock quote for Apple Computer.
Below the sources are seven processor objects, arranged into vertical columns, representing three data extraction processes.
On the far left and top, the "text near volume" processor object finds 64 characters after the word "Volume" on the page. Connected below that object is another processor named "volume." The "volume" processor uses the "Text Between" method to extract just the specific text from between the tags in which it is formatted when loaded. The "volume" object is connected in turn to the "MySQL Export" object. The MySQL Export object uses the name of its inputs as the database field name, so any results from the "volume" object will be inserted into the "volume" field of any MySQL database records created from running this process.
Likewise, in the middle and on the right, processor objects are used to extract the "ticker" symbol and price "quote" respectively from the source pages.
To extract the ticker, three processors are used, the first uses the "Text Near" method to extract text near the word "Ticker" as it appears in the header of the table where the data is found, then two "Text Between" processors to reduce the output of the "Text Near" processor to just the ticker symbol. The final processor in the set of three is named "ticker," so any output produced by this process chain will be inserted into the "ticker" field of the MySQL database records created.
Finally, in this example, a "quote" price is extracted from the page data by getting the text near "Last Trade" and then extracting from text between known tags in that.
Once these processors complete their result, they pass their output to the "MySQL Export" object to be inserted into the MySQL database.
The "Host" portion of the MySQL Export object should be configured specific to your setup, in this example, the export uses a local MySQL installation.
This image shows the configuration of the Query settings for the MySQL Export object in this example:
There are several fields which must be configured for the MySQL export object to work properly.
In this example, the data will be written to the MySQL database "test" table "stocks" (we'll look at the database directly in a moment).
The Source URL that the data comes from will be put into the MySQL database record field "url."
We are anticipating multiple entries to be made, since there are two sources, but should there be a mismatch in the number of inputs, we will ignore any overrun items.
Remember that it is very important that there are fields in the MySQL database table specified that match the names of the input processors, and any fields specified for the URL or process path name. If these names don't match, the MySQL Export will fail (MESSAGE IN LOG WINDOW).
Running this process creates in the following results in the MySQL database, shown here using the PHPMyAdmin software client interface (your MySQL client may differ).
This also shows the benefit of using the recommended technique of having auto-entered Record ID ('recid') and Time Stamp ('timestamp') fields.
Installing, Configuring and Using MySQL and PHPMyAdmin are beyond the scope of this document, but they are not difficult to do. Please see the "Helpful Links" page for links to useful resources to help you use the MySQL Export Tool.
[ top of page ] [ anthracite examples ] [ anthracite documentation ]
