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

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 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. — bathman
– bathman, Commented May 1, 2020 at 11:42
like if you don't mind using global libraries, apt install python3-sqlalchemy (but would suggest virtual environment instead) — bathman
– bathman, Commented May 1, 2020 at 11:43
I have installed python3-sqlalchemy global and my script run with postgres user. Hope to solve the trigger part now. — Georgian
– Georgian, Commented May 1, 2020 at 12:00
@Georgian I've created a community wiki from your answer - feel free to add details to it as you see fit once you have the privilege (you need 27 more rep for it). — 0Valt
– 0Valt, Commented Jul 5, 2021 at 22:00

0Valt · Accepted Answer · 2021-07-05 21:56:21Z

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;

Collectives™ on Stack Overflow

Run Python script from PostgreSQL function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related