Demo: Training the model for linear regression

- [Instructor] For our first demo, we're going to utilize the houses table that was created in the T-SQL script provided. If you recall, this contains only three fields with the ID field being the primary key, square foot being the label and price being the feature. That same script populates the table with 14 different records, and the sample is kept small intentionally. If we turn our attention over into the Python script, first we start by importing the correct modules that are needed for the code. We specify the name of the database server, the name of the actual database that we're using, and of course the driver. We then create an engine. We then query the houses table using the highlighted query string. Once that's executed, we then define our feature, which is the square foot and the label, which is the price column. We then need to separate that data into two sample sets, one for testing and one for training. The testing will essentially be 20% of the data, and that's specified as the fraction parameter within the sample function. We also specified a random state of 40. Next we create our training data set, also containing features and labels. Both data sets are provided and printed out to the user within the terminal window. Then we define the type of model we're going to be using, which is linear regression, and it's going to be stored in the variable called lr_model. We then call the fit method and we utilize the training data for the features as well as the training data for the labels. We then use our test data to actually make predictions, so we pass in our feature test into the predict method, and then from here we're able to evaluate and compute the error between both the test predictions and the actual values. So we're able to extract the mean squared error as well as the mean absolute error, and then we display those to the user. In addition, we're also displaying the R squared score. Once that's displayed to the user, we then utilize the dumps method within pickle to be able to extract that model into a binary field. We then store it into trained model. The next portion of the code then stores that model into the database, and if you recall, we have a models table that essentially is going to accept two parameters, the name of the model that we want to call it, and the actual model itself. Once that's executed, we then display to the user that the model has been stored successfully. You'll notice the code for storing the model in the database has been placed with inside a try-catch block, and here I want to catch any exceptions that may arise while I'm writing that model into the database. If everything goes as planned, I then close the connection to the database. Now let's execute the code and see what actually happens. Once I click run, it then opens up a terminal window and let me scroll to the top of it and we'll see that it displays our training features. Note the index, 1 through 9, 11, and 12. Same thing with the labels for training. 1 through 9, then 11 and 12, and the reason that's important is you'll see that the test data has different indices, zero, 13, and 10. This is because we want to train it on a set of data, but then we want to test it on a different set of data to make sure that it falls within the linear regression model. After the data is displayed to the user, then we display the metrics, the mean squared error, the mean absolute error, and R squared. You'll notice that we have a high R squared value, which means that the data is very close to the linear regression model. Then since the model is stored into the database, we display the message model stored successfully. If we glance over into SQL Server Management Studio, you'll see that the model's table was already created, and when we select from that table, there is one model that's stored with the name that we gave it, lr_model, and the date and time that it was created.