Postgres Stored Procedures with Python

Introduction

In this tutorial, we learn to create and use Postgres Stored Procedures with Python scripting. The lessons in this article include the use of:

Prerequisites

  • Basic understanding of how to script SQL, either with PostgreSQL (or MS SQL Server, Oracle, MySQL, etc.) alone, using the free PG Admin tool or some other relational db administration tool, or even by using a scripting (compiled or not, object-oriented or not) language (like Java, C#, ASP.Net, VB.Net, PHP, Javascript, Python, Node, Ruby, etc.) that provides a connection to your database as well as a method for sending it T-SQL statements, to query or make changes to your data.
  • Knowledge of the use of the most basic SQL commands, including SELECT, FROM, and INSERT.
  • Optional: Knowledge of Python.

What is a “Stored Procedure” and how does it work?

Features of Stored Procedures

  • Stored procedures (SPs) can also be known as “named programs” that are stored in the database, rather than in an application’s code.
  • SPs are error checked.
  • SPs are compiled objects.
  • SPs may or may not need parameters.
  • Default for SPs is to not return values.
  • Can not be executed or called from within a SELECT.
  • You can call an SP as often as you like.
  • Execution:
    • Explicit. EXECUTE, along with specific SP name and optional parameters.
    • Implicit. Uses only the Stored Procedure name.

Use of Stored Procedures

  • Modularity. When you are writing queries that are identical or similar, over and over again in an application, you can instead choose to write a stored procedure that can be called over and over from different parts of your application, with far less typing, potential typos, future maintenance and greater security, as discussed later.
  • Performance. The code is compiled only when created, meaning no need to compile at run-time, unless you change the Stored Procedure.
  • Security. Reduced chances of security breaches, including “SQL injection”.

For the bulk of this lesson, we’ll learn how to create a Stored Procedure using PostgreSQL’s structured query language “PL/pgSQL” or just “SQL”. Let’s begin with the syntax.

Postgres Stored Procedure Syntax

CREATE OR REPLACE PROCEDURE [proc name]([optional:params])
IS [OR "AS"]
[DECLARE]
    -- variables to execute
BEGIN
    -- statements to execute
[EXCEPTION]
    -- optional error handling statements
END;

Stored Procedure parameters

In Postgres Procedures, there are three kind of parameters:

  • IN (only for input).
  • IN OUT (in/out mode – both input and output).
  • OUT (only for output).

Stored Procedure Example

Let’s begin by creating a table named “tbl_technology” in PostgreSQL. Our company uses this table to keep track of the database and coding technologies used in the company.

CREATE TABLE tbl_technology (
    id serial NOT NULL,
    t_name VARCHAR(64) NULL,
    t_category VARCHAR(128) NULL,
    CONSTRAINT tbl_technology_pkey PRIMARY KEY (id)
    );
CREATE UNIQUE INDEX tbl_technology_id_idx ON tbl_technology USING btree (id);

Note: The id column is an auto-incrementing keyed index.

idt_namet_category
0OracleRDB
1MongoNoSQL
2MySQLRDB
3PostgreSQLRDB
4PythonLanguage
5PHPLanguage

Following is a query to create a new Postgres procedure called “ADD_TECH”:

CREATE OR REPLACE PROCEDURE ADD_TECH
    (
    P_t_name IN technology.t_name%TYPE,
    P_t_category IN technology.t_category%TYPE,
    P_t_msg_error OUT VARCHAR2
    )
IS

BEGIN
    INSERT INTO
    technology
    (
     t_name
     , t_category
    ) VALUES (
     P_t_name
     , P_t_category
    COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            P_t_msg_error := SQLERRM;
END ADD_TECH;

Analysis of the above SQL

  • CREATE: We use CREATE to add objects to the database. Here, we recommend getting into the practice of adding “OR REPLACE” to do automatic overwriting in case you ever make changes to the content of this Stored Procedure.
  • ADD_TECH: Naming the Stored Procedure. This is important because we need a name to use from our code to call (run/execute) the Procedure. Here we named the SP “ADD_TECH”.
  • PARAMETERS: Immediately after the name of our Stored Procedure are any [optional] parameters we choose to use with our PostgreSQL Procedure. Note how we named all our parameter variables with a “P_” prefix? This is required. Notice how two of our parameters are “IN” and one is “OUT”? The “IN” parameters are what get sent to the Stored Procedure from our code and the “OUT” parameter is what the SP sends back. We’ll see an example of this later.
  • %TYPE: If we don’t know the parameter variable type, we can use a sort of placeholder called %TYPE to set the type of this parameter to be dynamic. This will reduce security some and increase flexibility.
  • IS: This can also be “AS”. No difference. This option is for easier compatibility and ease of understanding if we are coming from other databases like Oracle, MySQL, or SQL Server.
  • BEGIN: This is how we inform Postgres of the beginning of what we want executed against the database, all the way to just before the END statement.
  • INSERT INTO: Here we are instructing the database, “You are about to receive values to put into the following two columns. Here are their names and the order they go in.
  • VALUES: This is to inform Postgres what exact values to put in the columns we defined above. Notice the “P” in front of our two values? Scroll up a bit in the SP code to be reminded how, in the PARAMETERS area we named each parameter with a “P“.
  • COMMIT: Instruct Postgres to follow the commands we gave it above this spot.
  • EXCEPTION: Here we tell PG what to do to handle an error.
  • WHEN OTHERS THEN: Here is the only part of the SP that sends data back out.
  • END: Marks the end of the BEGIN area and end of SP execution.

Calling the Postgres Procedure

Because Python works so well with Postgres, we’ll use this language for the example below.

from flask import Flask
import psycopg2 # for database connection and dependent on Flask
app = Flask(__name__)
dbconn = psycopg2.connect(t_dsn)
myCursor = dbconn.cursor()
myCursor.callproc('ADD_TECH', ('Python','Language'))
a_results = myCursor.fetchone()
MyCursor.close()
dbconn.close()

Assuming the connection and cursor objects are not something we need to recreate and destroy over and over – we have three lines of code taking the place of Python code that builds the following:

    INSERT INTO
    tbl_technology
    (
     t_name
     , t_category
    ) VALUES (
     'Python'
     , 'Language'
    COMMIT;

Note: The above SQL Statement doesn’t include the TRY…COMMIT…EXCEPT block of error-checking code that may look like this:

myCursor.execute(s)
    try:
        dbconn.commit()
    except psycopg2.Error as e:
        t_msg = "Database error: " + e + "/n SQL: " + s

Consider writing those INSERT INTO chunks of script over and over throughout your program but only changing what you send into those two fields? Do you see how much time and potential for typing errors you can save by using Postgres Stored Procedures instead?

Conclusion

Here we learned why, when and how to create Postgres Stored Procedures with Python to gain security, ease, power, and efficiency. We also looked at various other PostgreSQL functions and Python functions to demonstrate the benefits of using PostgreSQL Procedures. Finally, we included examples of our code in various spots during this lesson.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.