I have several stored procedures in a postgresql database that are run from a python script. They are all executed in the same fashion, and they all work except one. That stored procedure works fine, if I run it from from a query tool in pgAdmin, but it does nothing when it runs from the script. It doesn’t give an error; it just doesn’t do anything. It is supposed to delete an entry from one table and update a field in another table.
Here is a gist for the stored procedure: https://gist.github.com/algaspar/e7cb4157d2f3f70f8b47439e2a4058a4
Here is a gist for the script: https://gist.github.com/algaspar/3fa548379203aff9d887caed3c92cb64
I’ve trimmed the script down to what isn’t working. Basically, it reads in a file with a number of items and builds a list that it passes to the stored procedure with executemany (my sample just builds a list without reading a file, but it doesn’t work any better 🙁 ). I can run the procedure in query tool with:
CALL close_item('item1', CURRENT_DATE);
and it does what it is supposed to do–delete an entry from the itemchanges table and update a field in the itemhistory table. Does anyone have an idea why I shouldn’t be able to run this stored procedure from a script?
I’d really like to get an answer to this; so I’m not making this THE answer. I can solve my problem by running SQL in executes inside a loop. This accomplishes, what I need (having my list only contain items and no dates):
for item in my_list: cursor.execute ("DELETE FROM itemchanges WHERE item = %s", (item,)) ps_connection.commit() for item in my_list: cursor.execute ("UPDATE itemhistory SET fixed_date = CURRENT_DATE WHERE item = %s AND fixed_date IS NULL", (item,)) ps_connection.commit()
I’d like to know, though, why calling the stored procedure with an executemany didn’t work, when the stored procedure worked just fine as a query…