Calling a Python LP-solver from a PostgreSQL function


Prepare the database by installing PL/Python and a function:

CREATE EXTENSION IF NOT EXISTS plpythonu;

CREATE OR REPLACE FUNCTION hello_lp()
  RETURNS float[]
AS $$
  import subprocess
  output = subprocess.Popen (
    [
     "/Library/Frameworks/Python.framework/Versions/2.7/bin/python", 
     "/usr/local/trystuff/hello_lp.py"
    ], 
    stdout = subprocess.PIPE, 
    stderr = subprocess.STDOUT
  ).communicate()[0]
  # Parse string of comma-separated floats
  return map(lambda x: float(x), output.split(","))
$$ LANGUAGE plpythonu;

Install LP-solver (cvxopt):

pip install cvxopt

External Python script (save as /usr/local/trystuff/hello_lp.py):

from cvxopt import matrix, solvers

solvers.options['show_progress'] = False

A = matrix([ [-1.0, -1.0, 0.0, 1.0], [1.0, -1.0, -1.0, -2.0] ])
b = matrix([ 1.0, -2.0, 0.0, 4.0 ])
c = matrix([ 2.0, 1.0 ])
sol=solvers.lp(c,A,b)

# Print string of comma-separated floats
print ",".join([str(x) for x in sol['x']])

Try it:

SELECT unnest(hello_lp());

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.