Manage Postgres Tables with Python

Introduction

In this tutorial we will see how to use Postgres for managing tables with Python. As a learning exercise, we will build an application in Python to show a list of tables to the user and and give them the opportunity to delete any of the tables via a click or tap. We’ll start with setting up a Postgres database connection in with Python using the psycopg2 framework. We will then use Python and SQL to cycle through and display a list of tables in a dynamic HTML page for the user to interact with via Python’s render_template function from the Flask framework. Some other methods, commands, etc. we’ll be using include:

  • div tag for building the Python template.
  • Python List to store all records from a fetchall execution.
  • for loop to loop through all tables found in the database.
  • Drop table to delete a Postgres table based on user input.

Prerequisites

Following are a few technologies you may want to understand in order to get the most out of this lesson:

  • Python coding: We used Python 3.8, which is the latest version of Python as of the day when we wrote this article. You are not obligated to use this new a version. Either way, you can download Python free here.

  • Writing SQL: Some knowledge of how to write and execute queries for PostgreSQL. In the lessons here, we only need to use fairly simple queries, including use of SELECT, WHERE, and ORDER BY. For managing our Postgres database installation, we prefer dBeaver’s tool for its clear and easy-to-use interface, query debugging, and many other features.

The first item we will build for our Python application for managing Postgres tables is the dynamic HTML page that we’ll call up from Python with the render_template function. This page will display a list of tables that exist in the Postgres database.

Build dynamic HTML page

<html>
<head></head>
<body>
<div class='div_table'>
{%
for t_name_table in array_tables:
%}
    <div class='div_row'>
        <div class='div_column'>{{ t_name_table }}</div>
        <div class='div_column'><a href='{{ t_url }}?t_action=delete&t_name_table={{ t_name_table }}'>{{ t_name_table }}</a></div>
    </div>
{%
endfor
%}
</div>
</body>
</html>

Analysis

  • for t_name_table in array_tables: Loop through each item in the “array_tables” array (Python calls this a List), which was passed to this template. Also naming each item in the loop as “t_name_table”.
  • div: Used in HTML for encapsulation so that styles, javascript, and other treatments can be done to the content inside the div and close div tages. We did not include the css here necessary to lay the div contents out in a table-like format.
  • {{ t_url }}: We encapsulated this script in divs to style the html, as mentioned above. Next are the curly brackets. As you can see, two of them together tell the server to place values in that spot provided by Python. In this case, we are filling that spot with the contents of “t_url” and “t_name_table”. t_url is the URL for our application and t_name_table is the current for loop item from the array_tables list.
  • endfor: Typically with Python we don’t need a “next” or “endfor” type statement to designate the end of a for loop. Dyanmic HTML from a Flask template requires this because we are not relying on indenting to tell Python when the loop ends. We need it here so that it is apparent to the server that this is the “bottom” of our for loop.
  • Note: In addition to not including the specific CSS for styling our DIVs into a table structure, we left out detailing the html, body, and head tags, so you may want to flesh them out.

Now that we have created our dynamic HTML page that will be called from our Python application, make sure to save the file in a folder off the root of your server called “templates\”. Save the file as “tables_manage.html” in that folder.

Now pull up your VS Code or whatever IDE you use for creating, editing, and debugging your Python applications and we’ll build the application piece by piece. The first piece is to set up a database connection to Postgres from Python.

Postgres Database connection with Python

import psycopg2
t_host = "Postgres database host URL"
t_port = "5432"
t_dbname = "database name"
t_name_user = "database user name"
t_password = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = db_conn.cursor()

Analysis

  • import psycopg2: The psycopg2 framework with the components necessary for connecting to Postgres, executing queries, and creating a database cursor.
  • t_host: If your database is on your local computer, use “localhost”, otherwise, a URL or IP address will be needed.
  • t_port: “5432” is the default port for postgreSQL servers.
  • t_dbname: The name of your database.
  • t_name_user: The name you set up to access the Postgres database from Python.
  • t_password: Password for the above user.
  • db_conn: Connection to your PostgreSQL server.
  • db_cursor: Cursor for reading/writing from/to the database with Python.

Now for some Python code to get a list of all the tables in the Postgres database

Query to get Postgres tables

Heres some SQL to retrieve a list of tables from PostgreSQL that are in the “public” schema.

s = ""
s += "SELECT"
s += " table_schema"
s += ", table_name"
s += " FROM information_schema.tables"
s += " WHERE"
s += " ("
s += " table_schema = 'public'"
s += " )"
s += " ORDER BY table_schema, table_name;"

Analysis

  • table_schema: This stores whether the table is designated as public or not.
  • table_name: The name of the table being referenced.

Get all Postgres tables

Now that we used Python to write the Postgres SQL for retrieving a table list, we can execute the query and use a for loop to cycle through all the tables returned.

db_cursor.execute(s)
array_tables = db_cursor.fetchall()

for t_name_table in array_tables:
    print(t_name_table + "\n")

Analysis

  • array_tables…: The psycopg2 fetchall function returns all records returned from a query sent to Postgres. This record set is stored in the Python list we named “array_tables”.
  • for t_name_table: This is the meat of our for loop, assigning the “t_name_table” variable to track each iteration of a value stored in the “list_tables” list.
  • print: Returns the table name each time through the for loop iteration, along with “n”, which sends a carriage return. In our final application, we’ll leave out this function because we’ll be displaying the table names on the dynamic HTML page we built earlier.

Now that we have gone through the process for Postgres to list tables using Python, we’ll look at how we receive data from the user on the dynamic HTML page and branch within our Python application accordingly. We limited functionality of this application to merely deleting tables. An idea for future expansion would be to allow the user to create tables and edit tables, and even creating columns!

Retrieve data from dynamic HTML in Python

First, we’ll build an understanding of how to request data from a page or form using request. Our application will only be using the GET form of request, but we may as well also learn to use the POST form, which is for – mostly – retrieving data that was submitted via an HTML form.

Python Request Syntax

t_data = request.args.get([field name], [value if null received]) # for GET; querystring.
t_data = request.form.get(field name, [value if null received]) # for POST; form submission.

Analysis

  • field name: This determines the name of the field (how it was named in your querystring or form). For example, “t_action” in our application.
  • value if null received: In case no data was received, this is the value your t_data variable will receive. This is a way to deal with potential 404 errors. We often put an empty string here, like “”, as you will see in the example below.

Python Request Example

Here are some examples of how the syntax above might look when used in a project. We’ll leave out “get” as it has no relevance to our current learning:

t_action = request.args.get("t_action", "")
t_action = request.form.get("t_action", "")

Analysis

  • t_action: This part of the Python script is like asking the server, “Look at the querystring buried in the URL, find the ‘?’. After that question mark symbol, look for ‘t_action=’ and put the value that comes after that equal sign symbol into a local variable we named t_action.”
  • t_name_user: This line of code grabs the value the user put into the HTML field named “t_name_user” and places that value in the local Python text variable we called “t_name_user”.

That’s it for how “request” works. There will be a few other features – fairly simple – that we will be writing in the full application below with commenting. Please note, we left out error trapping and creating any functions, in order to keep this lesson as focused and simple for you as possible.

Source code to manage Postgres tables with Python

# -------------------------------
# Import frameworks and libraries
# -------------------------------
from flask import Flask
from flask import render_template # to render a dynamic HTML page
from flask import request # to get user input from a querystring
import psycopg2 # establish Postgres database connection

# ---------------------
# Connect to PostgreSQL
# ---------------------
t_host = "Postgres database address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default port for postgres server
t_dbname = "database"
t_name_user = "user name"
t_password = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = db_conn.cursor()

@app.route("/Main", methods=["POST","GET"])

# --------------------------
# Function to delete a table
# --------------------------
def DeleteTable(t_name_table):
    # delete table SQL
    s = ""
    s += "DROP TABLE IF EXISTS"
    s += " " + t_name_tbl
    # execute the delete table SQL
    db_cursor.execute(s)
    # if autocommit is off:
    db_conn.commit()

# ----------------------------------
# Function to list all public tables
# ----------------------------------
def GetTableList(t_schema):
    s = ""
    s += "SELECT"
    s += " table_name"
    s += " FROM information_schema.tables"
    s += " WHERE"
    s += " ("
    s += " table_schema = '" + t_schema + "'"
    s += " )"
    s += " ORDER BY table_schema, table_name;"
    # Retrieve all the rows from the cursor
    db_cursor.execute(s)
    return db_cursor.fetchall()
    db_cursor.close()

# ----------------------------
# Primary application function
# ----------------------------
def Main():
    # check querystring for action to take
    t_action = request.args.get("t_action", "")
    t_name_table = request.args.get("t_name_table", "")

    # if coming from user then delete a table
    if t_action != "" AND t_name_table != "":
        return DeleteTable(t_name_table)

    # Either way: get table list and pull up dynamic HTML page (template)
    t_schema = "public"
    array_tables = GetTableList(t_schema)
    # Show the dynamic HTML page to user
    t_url = "[URL to application]" # be sure to fill this in!
    return render_template("tables_manage.html", array_tables = array_tables, t_url = t_url)

Conclusion

We learned how to use PostgreSQL for managing tables with Python. We built a Python application to display a list of PostgreSQL tables to the user and and give them the opportunity to delete any of the tables via a click. We saw how to set up a PostgreSQL database connection with Python using the psycopg2 library; used Python, SQL, and a FOR loop to iterate through and show a list of tables on a dynamic HTML page for the user to interact with via Python’s render_template function from the Flask framework. Other methods, commands, etc. we used: div, Python List, For Loop, and Drop Table. Code samples included.

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.