PostgreSQL PL / Python: calling a stored procedure in virtualenv
When I call a PostgreSQL PL / Python stored procedure in my Python application, it appears to be executing in a separate process running as user postgres
. So far this has only had a side effect that I had to make my log file available to both myself and the database user, so the application and stored procedure can write to it.
Now, however, I started using virtualenv
and added a few files .pth
to my folder ~/.virtualenvs/virt_env/lib/python2.7/site-packages/
that added my module paths to the Python path.
When the stored procedure is executed, the user is postgres
not in the same virtual environment as me, so the stored procedure does not find my modules. I can change PYTHONPATH
in the global PostgreSQL environment , but I have to change this every time I switch virtual environments, which is not the purpose of virtualenv ...
How can I extend the Python path for stored procedures?
UPDATE
A was asked a similar question and was allowed to change the PYTHONPATH environment variable in Postgres; however, there seems to be no standard way to specify environment variables for PostgreSQL ; at least it's not a viable solution for Mac OSX.
source to share
There is a way to do this, as it turns out. As of version 1.6 or there, virtualenv comes with a script activate_this.py
that can be used to set up an existing interpreter to access this virtual file.
exec(open('/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py').read(),
dict(__file__='/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py'))
And as a fully implemented plpython function:
CREATE OR REPLACE FUNCTION workon(venv text)
RETURNS void AS
$BODY$
import os
import sys
if sys.platform in ('win32', 'win64', 'cygwin'):
activate_this = os.path.join(venv, 'Scripts', 'activate_this.py')
else:
if not os.environ.has_key('PATH'):
import subprocess
p=subprocess.Popen('echo -n $PATH', stdout=subprocess.PIPE, shell=True)
(mypath,err) = p.communicate()
os.environ['PATH'] = mypath
activate_this = os.path.join(venv, 'bin', 'activate_this.py')
exec(open(activate_this).read(), dict(__file__=activate_this))
$BODY$
LANGUAGE plpythonu VOLATILE
(Requires additional PATH mungery as the default PATH is not available in plpython os.environ - activate_this.py has a fixed set to which should roll with the next point version (which should be 1.11.7 or 1.12)
(taken mostly from https://gist.github.com/dmckeone/69334e2d8b27f586414a )
source to share
Usually I would say this is not a good idea, but you can follow this question .
What you can do is run multiple PostgreSQL instances with different environments to use different settings PYTHONPATH
.
source to share