Looking for a house (part 3)

Here the last part of my research: a ML model.

I will create a ML decision tree model with some data previously scraped and loaded in a Sql database.

First of all I create the training data:

in the column "eval" I insert an index from 1 to 3 (1= not interesting, 2=not bad ,3= interesting).

This table is used to train our model as done below:

The slqalchemy engine is created and directly passed as parameter in the pandas function pandas.read_sql().

The variables x and y are used to generate the training and the test data(without further parameter, the data are randomly splitted as 75% of data for training and 25% as test data).

With that we are able to create our model.

The model is provided from the library scikit-learn where the DecisionTreeClassifier is what I was looking for.

With the trained model we can feed it using the whole dataset to obatain the parameter "eval" for each house.

The script below is commented to clarify each step.

 

At the end some interesting houses (eval = 3) obtained from my model.

Looking for a house (part 2)

In my previous article (looking for house part 1 ) I described how I obtain data from a real-estate website.

With the a dedicated pipeline I pushed all data in a Couchdb database.

I prefer to store data in a NoSql database. In my case the Couchdb is something like a Datalake where the data are stored waiting to be processed  in a next time. With a Noslq database I am not constrain to keep a fix schema or a fixed datatype. In this way I am more flexible to add or remove Fields in the Scrapy item or adapt the spider to another html page.  

Couchdb has also a useful http/rest API (I use just the python library requests to create a new document) and with its sync feature I can easily synchronize the database between different Couchdb instances.

Here one document as example:

My target now is to export the Couchdb documents in a Postgres database. To achieve this target I use 2 other tools: the famous ORM Python library sqlalchemy and an ETL Tool, bonobo.

Here the complete data model: 

and the ER schema:

in the Couchdb_extract function I extract all the documents from the database: 

in the "transform" function the json documents have been mapped to the Slqalchemy Model defined at the beginning

finally in the "load" method inserts all the processed data in Postgres.

A bonobo service is needed to instantiate the connection with Postgres (more here).

the function get_graph chains all our  ETL functions together.

and in the main function we make the script executable through the command:

bonobo run etl_script.py

Now the data are ready to be analysed from a sql database.

How to save the scraped data from Scrapy to a mysql DB

After a simple example of a web spider( written  here) I will write a pipeline which will stored the data in a mysql database.

from the scrapy documentation:

After an item has been scraped by a spider, it is sent to the Item Pipeline which processes it through several components that are executed sequentially.

In order to process the item, in our case, we need a custom pipeline which stores the item in a mysql database.

I choose Sqlalchemy to store the items.



I have written here 3 methods, the first establishes a connection to the database (if you want to try it don't forget to insert your credentials), the second makes the table in the database and the third one create the model "job".

In Sqlalchemy the user-defined class rapresents the database table and the istance of those classes the table's rows. In our case the model "job" represents the table in the database

The Sqlalchemy Object relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. (from here).


After that we need to write our custom pipeline.

The init method provide a connection with the database.
The next method , process_item, is called for each item parsed. What it does, is simply to instantiate an object Job for each item and check if the item already exists in database or not. If it exists, a new line is added to the log file otherwise the pipeline inserts the item in database.

Now we just need to modify the file settings.py in this way:

That's all