Using virtualenv under PostgreSQL PL/Python

gistfile1.md

(All of this code tested in Enterprise DB PostgreSQL 9.4 Beta 1 and Python 3.3)

As of Virtualenv 1.3 (https://pypi.python.org/pypi/virtualenv) (and excluding Python 3.4's venv) there is an activate_this.py file within a virtualenv package that allows activation of a virtual environment within an embedded version of Python (like PostgreSQL's PL/Python). A Python 2/3 compatible version looks like this:

exec(open('/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py').read(), 
     dict(__file__='/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py'))

Where /Some/VirtualEnv/Directory is a directory and myvirtualenv is the name of the virtual environment in use. On Windows replace bin with Scripts.

(Also see the mod_wsgi take on this: https://code.google.com/p/modwsgi/wiki/VirtualEnvironments)

Once that is in place it seems to be possible to activate any virtual environment that is used by the Python interpreter inovked by PostgreSQL:

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:
        activate_this = os.path.join(venv, 'bin', 'activate_this.py')
        
    exec(open(activate_this).read(), dict(__file__=activate_this))
$BODY$
LANGUAGE plpython3u VOLATILE

The change does not seem to be reflected in site.getsitepackages() in a function like this:

CREATE OR REPLACE FUNCTION get_site_packages()
  RETURNS text[] AS
$BODY$
    import site
    return site.getsitepackages()
$BODY$
  LANGUAGE plpython3u VOLATILE

=# SELECT get_site_packages();

{/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages,/Library/Frameworks/Python.framework/Versions/3.3/lib/site-python,/Library/Python/3.3/site-packages}

But it is reflected in the sys.path:

CREATE OR REPLACE FUNCTION python_sys_path()
  RETURNS text[] AS
$BODY$
    import sys
    return sys.path
$BODY$
  LANGUAGE plpython3u VOLATILE;

=# SELECT python_sys_path();

{/Some/VirtualEnv/Directory/myvirtualenv/lib/python3.3/site-packages,/Library/Frameworks/Python.framework/Versions/3.3/lib/python33.zip,/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3,/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/plat-darwin,/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/lib-dynload,/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages}

With a simple test function like this (which imports SQLAlchemy and returns a list (PostgreSQL Array) of SQLAlchemy's attributes):

CREATE OR REPLACE FUNCTION use_sqlalchemy()
  RETURNS text[] AS
$BODY$
    import sqlalchemy
    return dir(sqlalchemy)
$BODY$
LANGUAGE plpython3u VOLATILE

We get the following output when an environment is not activated:

=# SELECT use_sqlalchemy()

ERROR:  ImportError: No module named 'sqlalchemy'
CONTEXT:  Traceback (most recent call last):
  PL/Python function "use_sqlalchemy", line 2, in <module>
    import sqlalchemy
PL/Python function "use_sqlalchemy"

And then the output with the activated virtualenv (containing SQLAlchemy)

=# SELECT workon('/Some/VirtualEnv/Directory/myvirtualenv'); -- Use 'myvirtualenv' virtual environment in '/Some/VirtualEnv/Directory' directory.
=# SELECT use_sqlalchemy();

"{BIGINT,BINARY,BLOB,BOOLEAN,BigInteger,Binary,Boolean,CHAR,CLOB,CheckConstraint,Column,ColumnDefault,Constraint,DATE,DATETIME,DDL,DECIMAL,Date,DateTime,DefaultClause,Enum,FLOAT,FetchedValue,Float,ForeignKey,ForeignKeyConstraint,INT,INTEGER,Index,Integer,In (...)"

The virtual environment will stay active until you switch to another virtual environment, or the session is closed. Put another way, the environment has PostgreSQL session lifetime.

Ran into an issue w/ this, using virtualenv > 1.7.1 (actually 1.11.4) - activate_this.py expects a PATH in os.environ, though my default plpythonu (I'm using python 2.7, PostgreSql 9.3) only has a few locale and PG specific values:
[ 'LANG', 'LC_NUMERIC', 'PGSYSCONFDIR', 'PG_GRANDPARENT_PID',
'LC_COLLATE', 'LC_CTYPE', 'LC_MONETARY',
'PGDATA', 'PWD', 'PGLOCALEDIR', 'LC_TIME', 'LC_MESSAGES']

So I added a test-and-set to the workon example:

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

Seems a fix was committed upstream for this:
pypa/virtualenv@6803ab9

That should land in the next point-release.