1

I would like to run records from my database (Postgres), after an update, through a machine learning model. This model resides in a model.py file.

Next to simply porting the python model over to a SP or UDF (I do not know which would be the best to use, if at all).

How might I call this python model and pass the record parameters to the model and return a desired result?

1 Answer 1

2

You have basically three options:

  1. Have a program written in Python, that uses PsycoPG, PyGreSQL or some other driver with equivalent functionality to get the data out of PostgreSQL and process it in Python. Or you could use some toolkit like SQLAlchemy, which works as an Object Relational Mapper.

  2. Write a User Defined Function using PL/Python, which means you actually write UDFs within PostgreSQL using the Python language. If your model works with a certain function model_predict(a, b, c), you can call it in a fashion similar to:

    SELECT a, b, c, model_predict(a, b, c) AS prediction FROM table WHERE <condition>

  3. Export the data from PostgreSQL (with COPY FROM ..., or using any tool such as pgAdmin), into a suitable format (such as CSV), and then import it and process in Python (you could use Pandas read csv, e.g.).

4
  • With OPTION #1 - Would PsycoPG/SQLAlchemy need to 'watching' for the changes if it was only to be called ON UPDATE?
    – ratchet
    Jan 10, 2017 at 23:28
  • 1
    Don't fully understant what you mean to be called "ON UPDATE"... If you have a program to be run outside of the database, it is up to you to decide at which point it should be set to run. If you need to react quickly to data changes within the database, I would recommend doing things within the database (via INSERT/UPDATE/DELETE triggers). Other solutions to let the outside world know that changes have occurred are possible, via LISTEN/NOTIFY, or via periodic polling. It takes some learning how to use it.
    – joanolo
    Jan 10, 2017 at 23:32
  • Your are correct in assuming I would like to react to changes to the data as quickly as possible. If the trigger were to fire the function when the record is updated, then using PsycoPG to INSERT into another table (storing the predictions). This is the desired functionality. I was not aware of LISTEN/NOTIFY before, thank you for that. As you stated, within the database is the nominal option, and I will move forward with that.
    – ratchet
    Jan 10, 2017 at 23:43
  • That link does not really describe how to create a udf in python. Got any better pointer? Dec 26, 2018 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.