Connect MindsDB with your Database (MySQL)

Zoran Pandovski

Learn how to connect MindsDB with your MySQL database.

In a previous blog, we covered how easy it is to connect MindsDB with a PostgreSQL Database, train the model, and make predictions. Today we will connect MindsDB with a MySQL database and stick to our ‘With a few lines of code’ motto!

Prerequisites

If you want to follow up on this example but you don’t have MySQL installed, check the MySQL installation guide. You can also install MySQL Workbench as a visualization tool for MySQL databases. The dataset that we will be using is the Hotel Booking Demand that can be downloaded here. It contains a variety of booking information across different hotels and we will try to predict potential booking cancellation. Note that you can use a different dataset but you will need to do a minor change to the SQL select query.

Connect MindsDB to MySQL

The first thing we need to do is to import the MySQL datasource and Predictor from MindsDB:

Next, create a connection to MySQL database and get the data:

The arguments sent to MySqlDS are:

  • query (required) - The String representation of the query used to retrieve the data from the table e.g. query=”SELECT hotel, time_of_arrival from bookings”.
  • user (required) - The name of the database user.
  • password (required) - The password of the database user.
  • port - The port number used for connecting to the MySQL host. The MySQL default one is 3306.
  • host - The MySQL database host. The default one is localhost. 
  • table (required) - The name of the MySQL table where the data is persisted.
  • database (required) - The name of the MySQL database where the data is persisted.

We’ve got the data from MySQL, so next, we need to train the model. The required arguments to train the model with MindsDB are:

  • from_data - The data used for training.
  • to_predict - The target variable that we want to predict.

And, to get the predictions, we call predict function:

The when argument sent to the predict is a dictionary used for single prediction. Each key is the name of the column and each value is a value of the cell e.g.

That’s all. The explanation method will provide predicted value, confidence and important missing information.

Code Example

Let's use the hotel booking data to predict the booking cancellations.

MindsDB response:

{
   'is_canceled': {
       'predicted_value': '1',
       'confidence': 0.9367,
       'explanation': {
           'prediction_quality': 'very confident',
           'important_missing_information': ['stays_in_week_nights', 'meal', 'country', 'market_segment', 'previous_cancellations', 'previous_bookings_not_canceled', 'reserved_room_type', 'deposit_type', 'agent', 'company', ]
       }
   }
}

By looking at the MindsDB’s response, we can see that the model is very confident (93%) that the reservation will be canceled (is_canceled 1). The missing important information as `meal`, `previous_cancellations`, `previous_bookings_not_canceled` could improve the model confidence or yield another predicted value.

Conclusion

To summarize in short, what you need to do to make a prediction from your MySQL database is to:

  • Connect to the MySQL database and get the data with MySqlDS datasource.
  • Train a new Predictor.
  • Use explanation returned from the Predictors' predict method.

Author Bio

Zoran is a full stack developer based in Macedonia. He works as MindsDB's senior full stack developer and works on everything from building and managing the website to supporting the open source product to working with users on their support questions.

Be Part of Our Community.

Join our growing community.