PostgreSQL Python: Call PostgreSQL Functions (original) (raw)

Summary: in this tutorial, you will learn how to call PostgreSQL functions from a Python program.

This tutorial picks up from where the Transaction Tutorial left off.

To call a PostgreSQL function from a Python program, you use the following steps:

First, create a new database connection to the PostgreSQL server by calling the connect() function of the psycopg2 module.

conn = psycopg2.connect(config)

The connect() method returns a new instance of the connection class.

Next, create a new cursor by calling the cursor() method of the connection object.

cur = conn.cursor()

Then, pass the name of the function and the optionally pass values to the callproc() method of the cursor object:

cur.callproc('function_name', (value1,value2))

Internally, the callproc() method translates the function call and input values into the following statement:

SELECT * FROM function_name(value1,value2);

Therefore, you can use the execute() method of the cursor object to call a function as follows:

cur.execute("SELECT * FROM function_name( %s,%s); ",(value1,value2))

Both statements have the same effect.

After that, process the result set returned by the function using the fetchone(), fetchall(), or fetchmany() method.

Finally, call the close() method of the cursor and connection objects to close the communication with the PostgreSQL database server:

cur.close()
conn.close()

Let’s take an example of calling a PostgreSQL function from Python.

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the suppliers database:

psql -U postgres -d suppliers

Second, execute the following command to create a new function called get_parts_by_vendors() that returns a list of parts by a specified vendor:

CREATE OR REPLACE FUNCTION get_parts_by_vendor(id INTEGER)
  RETURNS TABLE(part_id INTEGER, part_name VARCHAR) AS
<span class="katex-display"><span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML" display="block"><semantics><mrow><mi>B</mi><mi>E</mi><mi>G</mi><mi>I</mi><mi>N</mi><mi>R</mi><mi>E</mi><mi>T</mi><mi>U</mi><mi>R</mi><mi>N</mi><mi>Q</mi><mi>U</mi><mi>E</mi><mi>R</mi><mi>Y</mi><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi mathvariant="normal">.</mi><mi>p</mi><mi>a</mi><mi>r</mi><msub><mi>t</mi><mi>i</mi></msub><mi>d</mi><mo separator="true">,</mo><mi>p</mi><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi mathvariant="normal">.</mi><mi>p</mi><mi>a</mi><mi>r</mi><msub><mi>t</mi><mi>n</mi></msub><mi>a</mi><mi>m</mi><mi>e</mi><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi>I</mi><mi>N</mi><mi>N</mi><mi>E</mi><mi>R</mi><mi>J</mi><mi>O</mi><mi>I</mi><mi>N</mi><mi>v</mi><mi>e</mi><mi>n</mi><mi>d</mi><mi>o</mi><msub><mi>r</mi><mi>p</mi></msub><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi>o</mi><mi>n</mi><mi>v</mi><mi>e</mi><mi>n</mi><mi>d</mi><mi>o</mi><msub><mi>r</mi><mi>p</mi></msub><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi mathvariant="normal">.</mi><mi>p</mi><mi>a</mi><mi>r</mi><msub><mi>t</mi><mi>i</mi></msub><mi>d</mi><mo>=</mo><mi>p</mi><mi>a</mi><mi>r</mi><mi>t</mi><mi>s</mi><mi mathvariant="normal">.</mi><mi>p</mi><mi>a</mi><mi>r</mi><msub><mi>t</mi><mi>i</mi></msub><mi>d</mi><mi>W</mi><mi>H</mi><mi>E</mi><mi>R</mi><mi>E</mi><mi>v</mi><mi>e</mi><mi>n</mi><mi>d</mi><mi>o</mi><msub><mi>r</mi><mi>i</mi></msub><mi>d</mi><mo>=</mo><mi>i</mi><mi>d</mi><mo separator="true">;</mo><mi>E</mi><mi>N</mi><mi>D</mi><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">BEGIN
 RETURN QUERY

 SELECT parts.part_id, parts.part_name
 FROM parts
 INNER JOIN vendor_parts on vendor_parts.part_id = parts.part_id
 WHERE vendor_id = id;

END;</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.9805em;vertical-align:-0.2861em;"></span><span class="mord mathnormal">BEG</span><span class="mord mathnormal" style="margin-right:0.07847em;">I</span><span class="mord mathnormal" style="margin-right:0.13889em;">NRET</span><span class="mord mathnormal" style="margin-right:0.10903em;">U</span><span class="mord mathnormal">RNQ</span><span class="mord mathnormal" style="margin-right:0.10903em;">U</span><span class="mord mathnormal" style="margin-right:0.00773em;">ER</span><span class="mord mathnormal" style="margin-right:0.22222em;">Y</span><span class="mord mathnormal" style="margin-right:0.05764em;">SE</span><span class="mord mathnormal">L</span><span class="mord mathnormal">ECTp</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mord">.</span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mord">.</span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">n</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">am</span><span class="mord mathnormal">e</span><span class="mord mathnormal">FROMp</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.07847em;">I</span><span class="mord mathnormal" style="margin-right:0.00773em;">NNER</span><span class="mord mathnormal" style="margin-right:0.09618em;">J</span><span class="mord mathnormal" style="margin-right:0.02778em;">O</span><span class="mord mathnormal" style="margin-right:0.07847em;">I</span><span class="mord mathnormal" style="margin-right:0.10903em;">N</span><span class="mord mathnormal" style="margin-right:0.03588em;">v</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal">d</span><span class="mord mathnormal">o</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">p</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">so</span><span class="mord mathnormal">n</span><span class="mord mathnormal" style="margin-right:0.03588em;">v</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal">d</span><span class="mord mathnormal">o</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">p</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mord">.</span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mord">.</span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">W</span><span class="mord mathnormal" style="margin-right:0.08125em;">H</span><span class="mord mathnormal" style="margin-right:0.05764em;">ERE</span><span class="mord mathnormal" style="margin-right:0.03588em;">v</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal">d</span><span class="mord mathnormal">o</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mpunct">;</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.10903em;">EN</span><span class="mord mathnormal" style="margin-right:0.02778em;">D</span><span class="mpunct">;</span></span></span></span></span>

LANGUAGE plpgsql;

Notice that you can use any PostgreSQL client tools to create a function such as pgAdmin.

First, create a new module in the project directory called call_function.py:

Second, define a new function called get_parts() that calls the get_parts_by_vendors() function in PostgreSQL:

import psycopg2
from config import load_config


def get_parts(vendor_id):
    """ Get parts provided by a vendor specified by the vendor_id """
    parts = []
    # read database configuration
    params = load_config()
    try:
        # connect to the PostgreSQL database
        with  psycopg2.connect(**params) as conn:
            with conn.cursor() as cur:
                # create a cursor object for execution
                cur = conn.cursor()
                cur.callproc('get_parts_by_vendor', (vendor_id,))

                # process the result set
                row = cur.fetchone()
                while row is not None:
                    parts.append(row)
                    row = cur.fetchone()

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        return parts

if __name__ == '__main__':
    parts = get_parts(1)
    print(parts)

Run the following command to execute the call_function.py module:

python call_function.py

Output:

[(1, 'SIM Tray'), (5, 'Home Button'), (6, 'LTE Modem')]

Download the project source code