Run Python script from PostgreSQL function

Question

I'm facing a "little" problem regarding executing a Python script every time is an update or insert action on the PostgreSQL table.

This script will extract and write to a file updated or inserted data.

Environment data: Ubuntu 18.04 (Bionic Beaver), PostgreSQL 10, and Python 3.6

SELECT * FROM pg_available_extensions
WHERE name LIKE '%python%' ORDER BY name;

And output

name	default_version	installed_version	comment
hstore_plpython2u	1.0		transform between hstore and plpython2u
hstore_plpythonu	1.0		transform between hstore and plpythonu
ltree_plpython2u	1.0		transform between ltree and plpython2u
ltree_plpythonu	1.0		transform between ltree and plpythonu
plpython2u	1.0		PL/Python2U untrusted procedural language
plpythonu	1.0	1.0	PL/PythonU untrusted procedural language

I've created a PostgreSQL function (I hope it to be ok after all documentation readings)

CREATE FUNCTION getSomeData()
RETURNS trigger
AS $$
begin
import subprocess
subprocess.call(['/usr/bin/python3', '/some_folder/some_sub_folder/get_data.py'])
end;
$$
LANGUAGE plpythonu;

After this, create trigger

CREATE TRIGGER executePython
AFTER INSERT ON mytable
EXECUTE PROCEDURE getSomeData();

Nothing happens if I'm making any insert or update.

As an additional precaution, I did the following test

sudo -u postgres python3 /some_folder/some_sub_folder/get_data.py

and got this output:

Traceback (most recent call last):
File "/some_folder/some_sub_folder/get_data.py", line 4, in <module>
from sqlalchemy import create_engine
ImportError: No module named sqlalchemy

I have installed SQLAlchemy globally and now my script runs as expected with the postgres user, but it does not trigger.

apt install python3-sqlalchemy

Answer 1

The pointer to the solution provided by bathman:

when you execute this as your usual user, do you execute it with the global python3, or is there a chance, you have venv or other environment loaded and your usual user python3 executable is an alias to your venv, where you locally installed SQLAlchemy? you could try to execute by /path/to/your/environment/with/sqlalchemy/installed/bin/python3 or installing SQLAlchemy globally so your /usr/bin/python3 has access to it.

The solution:

CREATE FUNCTION getSomeData()
RETURNS trigger
AS $$
begin
import subprocess
subprocess.call(['/path/to/your/virtual/environment/bin/python3', '/some_folder/some_sub_folder/get_data.py'])
end;
$$ 
LANGUAGE plpythonu;