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 Comment Cancel Reply