Postgres Views from Python

Introduction

In this tutorial we will study how to use Postgres views from Python. When are views in Postgres most often used? At times a query of a certain level of complexity is better packaged as a View, so as to create more modularity and efficiency in your Python application. Views are not the same as Stored Procedures, but are similar. In this tutorial, we will learn to create, update, and utilize PostgreSQL Views.

Prerequisites

  • High-level knowledge of how building SQL queries for PostgreSQL (or Microsoft SQL Server, Oracle, MySQL, Cockroach, etc.), using the free PG Admin tool or some other relational db administration tool, and/or by using code or script (compiled or not, object-oriented or not) languages like Python, PHP, Java, C#, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that allows you to create a connection to Postgres from Python as well as a method for executing SQL statements against your Postgres database.
  • Understanding of the use of basic SQL commands and structures, including table, column, SELECT, FROM, JOIN, and WHERE.
  • PURELY OPTIONAL: Naming conventions
  • PURELY OPTIONAL: Stored procedures

What is a Postgres view?

A VIEW in PostgreSQL is almost an SQL query. You can create, name, and call upon views as if they are tables. Random interesting fact: Microsoft Access – since version 1 – has included the ability to create “saved queries” that are exactly what a PostgreSQL View is in terms of appearance and functionality. Views tend to be more useful in a normalized database.

Postgres create view

Create view syntax

CREATE OR REPLACE
    VIEW name_of_view
    (
        col_name_1
        , col_name_2
        , etc
    )
    WITH
    (
        name_option [= VALUE] [, ... ]
    )
    AS myQuery;

Analysis of the this example above line-by-line:

  • CREATE OR REPLACE instead of just CREATE so we don’t get overwrite errors. You can use just CREATE in case you want to trap errors and react accordingly to a view with the same name existing already because your situation dictates that you need to know if that is the case. We’re taking a less complex route here. That said, we do recommend you use error trapping every time you execute SQL from Python. We’ll illustrate this later when we show how to create PostgreSQL views from Python.
  • name_of_view is the name you give your View so you can call it into action from anywhere, including within other queries. Keep in mind that Postgres treats Views just like it does tables.
  • col_name_1, etc. This is the structure you would use to specify column names from Postgres’ source table.
  • WITH is optional. It refers to any optional parameters you want to feed to your view.
  • myQuery is your SELECT or VALUES statement that gives the view its rows and columns.

Note about columns returned

Your new query returns the same columns that were created by the existing view. This means the same columns in the same order and with the same data types, but it may also add new columns to the end of the dataset that is returned. If the column names are not given, the query supplies them. The functions or operations generating the output columns may be different.

Let’s use that syntax to create a view of our own. We’ll start with two tables.

tbl_tech table:

id_techt_namei_category
0Cockroach5
1Mongo6
2MySQL5
3PostgreSQL5
4Python7
5PHP7

categories table:

idt_name_category
5RDB
6NoSQL
7Language

Before we go further, please note that in tbl_tech, in the i_category column, the numbers you see there refer to the id column in tbl_categories.

Normally, if we were not using Postgres, and having no ability to create and use Views or Stored Procedures, we would query the data for a human-readable report by using a join:

SELECT
    tbl_tech.id_tech
    , tbl_tech.t_name
    , tbl_tech.i_category
    , tbl_category.t_name_category
FROM tbl_category
INNER JOIN tbl_tech ON tbl_category.id = tbl_tech.i_category;

Which returns the following recordset:

![Postgres view of data returned from joined tables](https://i.gyazo.com/a57f7b3105ea2fb5dc14eac8f2ad1a0f.png “Postgres view of data from joined tables”)

Next, we convert the query into a View. We’ve already written all the code above and merely need to put it together. Here’s what it looks like when we have finished:

CREATE OR REPLACE VIEW view_tbl_tech AS
SELECT
    tbl_tech.id_tech
    , tbl_tech.t_name
    , tbl_tech.i_category
    , tbl_category.t_name_category
FROM tbl_category
INNER JOIN tbl_tech ON tbl_category.id = tbl_tech.i_category;

Notice how the only change to our query – in order to turn it into a view – was to add the following line of code at the beginning before the SELECT clause:

CREATE OR REPLACE VIEW view_tbl_tech AS...

You can now reference that View by the name view_tbl_tech anywhere you would normally reference a table.

Postgres View and Python

from flask import Flask # for redirect and render_template
from flask import render_template # to render the html error page
import psycopg2 # for database connection

app = Flask(__name__)

# Postgres database connection configuration
t_host = "database address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "database user password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

@app.route("/ViewTest", methods=["POST","GET"])
def ViewTest():
    s = ""
    s += "CREATE OR REPLACE VIEW view_tbl_tech AS"
    s += " SELECT"
    s += " tbl_tech.id_tech"
    s += ", tbl_tech.t_name"
    s += ", tbl_tech.i_category"
    s += ", tbl_category.t_name_category"
    s += " FROM tbl_category"
    s += "INNER JOIN tbl_tech ON tbl_category.id = tbl_tech.i_category;"

    # Error catching using TRY to commit the execution of our SQL.
    try:
        db_cursor.execute(s)
        db_conn.commit()
    except psycopg2.Error as e:
        t_msg = "Database error: " + e + "/n SQL: " + s
        return render_template("error.html", t_message = t_msg)
    db_cursor.close()

# Redirect user to the next stage of your application
return redirect("continue.py", code=302)

Miscellaneous

  • As mentioned above, We added OR REPLACE to the CREATE VIEW statement, which updates our view with whatever new code you put after that CREATE OR REPLACE VIEW view_tbl_tech AS part.
  • Another way to update a view is if you want to change the name of a view. To do this, you would use a command like the following: ALTER VIEW view_tbl_tech RENAME TO tbl_tech_joined.
  • You don’t have to name your view with the prefix “view_”. That is just the naming convention we use so as to hopefully provide as much clarity as possible.
  • Finally, if you want to completely delete your view, you can use the following command will do the trick:

Postgres drop view

DROP VIEW view_tbl_tech;

Conclusion

We studied how to use Postgres views from Python and standing alone. We investigated some when views are used in Postgres; seeing that sometimes a query of a certain complexity is better packaged as a View, so as to create more modularity and efficiency in Python applications.

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.