How to run function with parameters in python psycopg2-CodePudding

I have following function:

CREATE OR REPLACE FUNCTION f_update_in_steps(_oldVal VARCHAR, _newVal VARCHAR)
  RETURNS void AS
$func$
DECLARE
   _step int;   -- size of step
   _cur  int;   -- current ID (starting with minimum)
   _max  int;   -- maximum ID
BEGIN
   SELECT INTO _cur, _max  min(id), max(id) FROM "EndOfTheDay_change_test";
                                        -- 100 slices (steps) hard coded
   _step := ((_max - _cur) / 100)   1;  -- rounded, possibly a bit too small
                                        --  1 to avoid endless loop for 0
   PERFORM dblink_connect('dbconnectionparameters'); -- your foreign server as instructed above

   FOR i IN 0..200 LOOP                 -- 200 >> 100 to make sure we exceed _max
      PERFORM dblink_exec(
       'UPDATE "EndOfTheDay_change_test"
         SET    symbol = ''' || _newVal || '''
         WHERE  id >= ''' || _cur || '''
         AND    id < ''' || _cur   _step || '''
         and symbol = ''' || _oldVal || '''
         AND    symbol IS DISTINCT FROM ''' || _newVal || '''');  -- avoid empty update

      _cur := _cur   _step;

      EXIT WHEN _cur > _max;            -- stop when done (never loop till 200)
   END LOOP;

   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

select  f_update_in_steps('apple', 'AAPL');

This function just change one string on another in column (like 'apple' changing on 'AAPL')

I want to call this function in python code when necessary with passing parameters (_oldVal and _newval)

But I'm new for it, so I don't know how to do it...

CodePudding user response：

Like this:

import psycopg2

# Connect to the database
conn = psycopg2.connect(dbname="your_db_name", user="your_user", password="your_password", host="your_host")

cur = conn.cursor()

oldVal = 'apple'
newVal = 'AAPL'
cur.execute("SELECT f_update_in_steps(%s, %s)", (oldVal, newVal))

conn.commit()
cur.close()
conn.close()

Your can see more here and here.