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.