How to execute a Postgresql function from Python

Question

I have a Postgresql function as below:

CREATE OR REPLACE FUNCTION accdisvalues(thisdate date)
 RETURNS void AS
$BODY$
 Update my_table 
 Set 
 mycolumn = true
 where mydatecol = thisdate;
$BODY$
 LANGUAGE sql VOLATILE SECURITY DEFINER
 COST 100;
ALTER FUNCTION accdisvalues(date)
 OWNER TO myconnect;

Whenever I run it from PG AdminIII it runs perfectly and values are updated in the table.

I am trying to run it from Python 3 with the below code and the code runs without throwing an error but no update is done in the table. Could someone assist me troubleshoot what could be the problem? Many thanks.

import easygui
import dbconnex
 setdate = '30-Dec-2014'
 accdisupdate_fn = "Select accdisvalues('" + setdate + "');"
 print(accdisupdate_fn)
 cursor = dbconnex.conn.cursor()
 cursor.execute(accdisupdate_fn)
 easygui.msgbox(msg='Data loaded successfully for ' + setdate ,title="Input Status")

• Patrick: Is there some entry in the PG log file why the update fails?
• Avagut: Turns out after executing the cursor statement you need to run a conn.commit() to finish. Thank you very much for your assistance.

Answer 1

The full corrected Python code is below:

import easygui
import dbconnex
 setdate = '30-Dec-2014'
 accdisupdate_fn = "Select accdisvalues('" + setdate + "');"
 print(accdisupdate_fn)
 cursor = dbconnex.conn.cursor()
 cursor.execute(accdisupdate_fn)
 **dbconnex.conn.commit** #Pushes all changes to the DB
 easygui.msgbox(msg='Data loaded successfully for ' + setdate ,title="Input Status")

See here: http://initd.org/psycopg/docs/connection.html#connection.commit