Python sqlalchemy (postgresql) not returning real table state

Question

This is next chapter of my problem when Run python script from PostgreSQL function.

I will appreciate any further help or idea.

I've created that function on PostgreSQL

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

And trigger

CREATE TRIGGER executePython 
AFTER INSERT OR UPDATE OR DELETE ON mysensor
FOR EACH ROW EXECUTE PROCEDURE callMyApp();

With get_data.py

from sqlalchemy import create_engine

SQLALCHEMY_DATABASE_URI = 'postgresql:///database_name'

db = create_engine(SQLALCHEMY_DATABASE_URI)
some_file = "my_file.txt"

#store pair of id and value
someDictionary = {}
with db.connect() as dbcon:
#Get sensor unique ID and room name for every room_id from mysensor existent in rooms
    mydata = dbcon.execute('SELECT mytable1.uid, mytable2.name FROM mytable1,mytable2 WHERE mytable1.some_id = mytable2.id')
    for row in mydata:
        # add key,value from touple to dictionary
        someDictionary[row[0]] = row[1]


f = open(config_file,"w")
#write section name in config file
f.write("[somepairing]\n")

for key, value in someDictionary.items():
    print(key, value)
    f.write(key + "=" +value+"\n")
f.close()

When I run this script through my Postgresql trigger/function I get data with a step behind my current action.

If I make an insert now, my script will return nothing (suppose my table was empty). If I make another action like insert, delete, update, my script will log data from my first insert and not reflecting my current table content, and so on.

Example

Database state

What I get with my script after the trigger has been triggered

If I run this python script alone, not thru trigger, everything is fine and get exact table status.

Thank you very much for your help.

I've edited my code according and now my function is in that form

CREATE FUNCTION callMyApp()
RETURNS trigger
AS $$
import sys
sys.path.insert(0, '/path/to/my/module')
import get_config
return get_config.get_data()
$$ LANGUAGE plpythonu;

The new problem is that my modules imported in get_config are not recognized / the script does not know how to locate.

Error: ERROR:  ImportError: No module named sqlalchemy

The script uses its own session and so transaction. The default transaction isolation level is READ COMMITTED. If you want to be able to read from the current transaction in the trigger, I would suggest turning your `get_data` to a library and using it in the trigger, passing the [database access object](https://www.postgresql.org/docs/current/plpython-database.html) to the functions as an argument. — Ilja Everilä, May 02 '20 at 06:49
@IljaEverilä I have updated my code according with your suggestion . I've imported my script as module in postgresql /plpythonu function. CREATE FUNCTION callMyApp() RETURNS trigger AS $$ import sys sys.path.insert(0, '/path/to/my/module') import get_config return get_config.get_data() $$ LANGUAGE plpythonu; The other problem i'm facing is not recognizing modules requested by my module/python script. — Georgian, May 02 '20 at 12:42
This'll not solve possible other env/module issues, but in this case I'd suggest not using SQLA at all, but the `plpy` module provided to your UDF, passing it to the library functions as needed. You'll have to get familiar with it, but it's not too different from other DB APIs for Python. I'll try and come up with a proper answer instead of just comments later today. — Ilja Everilä, May 02 '20 at 14:08

score 0 · Accepted Answer · edited Jan 07 '21 at 15:04

Solved this situation. I'm sure exists a better way to solve it but for the moment it's all I need .

Step 1 - Create function

CREATE FUNCTION callMyApp()
RETURNS trigger
AS $$
import sys
sys.path.insert(0, '/path/to/my/module')
import my_module
myresult = plpy.execute('some_query_here',100)
list2=""
for i in range(len(myresult)):
    list2 = list2+ myresult[i]["some_table_row_here_returned_from_query"]+","
list3=""
for j in range(len(myresult)):
    list3 = list3+ myresult[j]["another_table_row_here_returned_from_query"]+","

return get_configV2.get_data(list2,list3)
$$ LANGUAGE plpythonu;

Step 2 - Create trigger

CREATE TRIGGER executePython
AFTER INSERT OR UPDATE OR DELETE ON mysensor
EXECUTE PROCEDURE callMyApp();

Step 3 - python script

def get_data(received1,received2):
    config_file = "/some/path/here/file.txt"
def convert(string):
    mylist = list(string.split(","))
    return mylist
f = open(config_file,"w")

#write section name in config file
f.write("Ziua buna din Braila!\n")

kkk = convert(received1)
ppp = convert(received2)
for i in range(len(kkk)):
    if (kkk[i] != ""):
        output = kkk[i]+"="+ppp[i]+"\n"
        f.write(output)
f.close()

Hope to be helpful and save your time. Thanks for your support Ilja Everilä

Python sqlalchemy (postgresql) not returning real table state

1 Answers1