Connect MindsDB with your Database (PostgreSQL)

Zoran Pandovski

Learn how to Connect MindsDB with PostgreSQL database
Download

One of the advantages of MindsDB is its simplicity. MindsDB allows anyone to learn and predict from their data without previous machine learning experience. But is there a simple way to export the data we need to train our models if it is persistent in the database? MindsDB offers a few solutions to that question. It offers options to ingest the data from different Databases such as PostgreSQL, MySQL and MariaDB. In the following example, you will learn how easy it is to connect MindsDB to your PostgreSQL database, train the model and get the predictions. 

Our motto is With a few lines of code so let’s stick to it and use a few lines for this example too.

Connect MindsDB to PostgreSQL

The first thing we need to do is import the Predictor and PostgreSQL Datasource:

Next, instantiate the PostgresDS datasource:

The arguments sent to the PosgresDS are:

  • query - The String representation of the Query used to retrieve the data from the table e.g. query=”SELECT firstName, lastName from Users”
  • user - The name of the database user.
  • password - The password of the database user.
  • host - The database host. Usually, this is localhost or the IP address of the host e.g 127.0.0.1
  • database - The name of the database where the data is persisted.
  • port - The port number used for connecting to the host. (The PostgreSQL default one is 5432.)
  • table - The name of the table where the data is persisted.


After successful connection to the database, we need to send the following required arguments to the MindsDB Predictor:

  •  from_data - The actual data that we got from the database.
  • to_predict - The target variable that we want to predict.

The model was created so now let’s get the predictions:

That’s all. We have successfully connected MindsDB to PostgreSQL, trained a new model and obtained the prediction.

Code Example

The following code example uses a heart-disease database to predict the presence of heart disease in patients. With a few lines we can select the data, train the model to predict the target variable and predict the presence of heart disease in the patient.

MindsDB response:

{'target': {'predicted_value': '1', 'confidence': 0.8207, 'explanation': {'prediction_quality': 'very confident', 'important_missing_information': ['trestbps', 'thalach']}

Following MindsDB’s response, we can see that the model predicted the presence (predicted_value 1) of heart disease for the patient with 0.8207 confidence. MindsDB also offers a suggestion for the additional information as trestbps and thalach that would improve the confidence if we include them in the when clause.

Conclusion

To summarize: to connect MindsDB to PostgreSQL and use the model to get the predictions:

  • First - Create a PostgresDS Datasource and add a query to select the data.
  • Second - Train a new Predictor and add the target variable.
  • Third - Use the Predictor to make the predictions.

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.