Creating and Deleting Postgres Tables

Introduction

This is a guide to creating and deleting Postgres tables. As an exercise, we’ll create a web application in Python to show all the tables in a database, along with HTML tools to delete and add new tables. We’ll begin by setting up a database connection to PostgreSQL using Python’s psycopg2 library. We will cycle through and display a list of tables in a dynamic HTML page for the user to interact with using many features of both Python and Postgres, including:

  • render_template: A function from the Python Flask framework.
  • Python List: Python’s version of an array that we will use to hold records from using the psycopg2 Fetchall function.
  • for loop: Python loop structure to iterate through the tables in our Python List object.
  • div: HTML tag used in our dynamic page.
  • Drop table: SQL command for deleting a PostgreSQL table. We’ll place an “x” next to every table shown to the user, so when they click that “x”, the corresponding table will get deleted.

Prerequisites

In order to get as much out of this lesson as possible, as easily as possible, here are some tools you may wish to have a cursory understanding of:

  • Python scripting: We used Python 3.8.1, which is the latest version of Python as of the day this article was written. You are not required to use this version as most, if not all, of the code we write here will run on older versions. If you want the latest, you can get Python here free.

  • Basic SQL: Some knowledge of how to write SQL for Postgres. In this guide, we used simple queries, including use of SELECT, WHERE, and ORDER BY. For managing our Postgres database installation, writing and testing queries, and filling tables with test data, we used dBeaver’s utility for its clear and easy-to-use interface, query debugging, and many other features. If you want a bit more bare bones, you can use PGadmin, which may come with your Postgres installation.

Now load VS Code or whatever IDE you prefer 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.

Python Database connection to PostgreSQL

import psycopg2
t_host = "[Postgres database 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 (or library) provides components we need for connecting to Postgres, executing SQL, and other data-related features.
  • t_host: If your Postgres runs is on your local computer, use “localhost”. If not, an IP address or URL will be needed.
  • t_port: “5432” is the default for PostgreSQL port.
  • t_dbname: The database name. Each Postgres install can have multiple databases in it.
  • t_name_user: The name you set up to have read and write permissions for the PostgreSQL database.
  • t_password: The database user’s password.
  • db_conn: Connection object used primarily to execute queries.
  • db_cursor: Cursor for reading and writing from and to PostgreSQL.

Next we will write some SQL script in Python to retrieve a table list from the database.

List all Postgres tables

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

Analysis

  • WHERE (table_schema = ‘public’): This filters our recordset to make sure we don’t get a list of the system tables, which we definitely do not want the user to make changes to.
  • table_name: The name of the tables.
  • list_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 “list_tables”.

List all Postgres tables

Now we will iterate through all the public tables using a for loop.

for t_table_name_current in list_tables:
    print(t_table_name_current + "\n")

Analysis

  • for t_table_name_current: This sets up our loop, creating the “t_table_name_current” text object to track each iteration of values in the “list_tables” array.
  • print: Shows t_table_name_current each iteration through the for loop, along with a carriage return via string concatenation. You won’t see this function in the final application at the end of this guide.

Now that we have gone through the process of connecting to Postgres and retrieving a list of tables to loop through, we will look at how to build a dynamic HTML page to be called from Python’s the render_template function. This HTML page will display the table list that exists in list_tables and is sent to the dynamic HTML page as a parameter that is part of the render_template function.

Later, we will explore retrieving data from the user via querystring from the HTML template and call different functions in our Python application based on the user’s actions.

Build dynamic HTML page

<html>
<head>
<title>Creating and Deleting Tables for PostgreSQL</title>
{%
list_types = ["boolean", "integer", "real", "varchar"]
t_box = ["", "", "", "", ""]
for x in range(5)
    t_box[x] = "<select name='t_data_type_" + (x):text + "'>" + "\n"
    for t_type in list_types:
        t_box[x] += "<option name='" + t_type + "'>" + t_type + "</option>" + "\n"
    endfor
    t_box[x] += "</select>"  + "\n"
endfor
%}
<!-- ANALYSIS -->
<!-- list_types is a Python list to store the four types of data for our select boxes -->
<!-- t_box is a Python list for storing the code for those boxes, so each box can have -->
<!--    a different name designation -->
</head>
<body style='background-color: EEEEEE;'>
<div class='heading'>Add new table</div>
<div class='div_form'>
<form name='addTable' action='{{ t_url }}?t_do_next=TableCreate' method='post'>
<div class='form_caption'>Table Name</div>
    <div class='form_input'><input name='t_name_table' type='text'></div>
<!-- below, instead of listing five nearly identical lines of inputs, we  -->
<!-- could choose to surround ONE of those with another Python FOR loop -->
<!-- which would be more elegant AND allow for more ease and growth for -->
<!-- allowing user to add as many fields as they want to any given new table -->
<!-- but for now, we are choosing the more repetitive route, for greater -->
<!-- simplicity and quicker understanding: -->
<div class='form_caption'>Field 1 Name</div>
    <div class='form_input'><input name='t_name_field_0' type='text'></div>
    <div class='form_input'>{{ t_box[0] }} </div>
<div class='form_caption'>Field 2 Name</div>
    <div class='form_input'><input name='t_name_field_1' type='text'></div>
    <div class='form_input'>{{ t_box[1] }} </div>
<div class='form_caption'>Field 3 Name</div>
    <div class='form_input'><input name='t_name_field_2' type='text'></div>
    <div class='form_input'>{{ t_box[2] }} </div>
<div class='form_caption'>Field 4 Name</div>
    <div class='form_input'><input name='t_name_field_3' type='text'></div>
    <div class='form_input'>{{ t_box[3] }} </div>
<div class='form_caption'>Field 5 Name</div>
    <div class='form_input'><input name='t_name_field_4' type='text'></div>
    <div class='form_input'>{{ t_box[4] }} </div>
<div></div><div><input name='go' type='submit' value='Create Table'></div>
</form>
</div>
<div class='heading'>Table list</div>
<div class='div_table'>
{%
for t_table_name_current in list_tables:
%}
    <div class='div_row'>
        <div class='div_column'>{{ t_table_name_current }}</div>
        <div class='div_column'><a href='{{ t_url }}?t_do_next=TableDelete&t_table_name_current={{ t_table_name_current }}'>{{ t_table_name_current }}</a></div>
    </div>
{%
endfor
%}
</div>
</body>
</html>

Analysis

  • The form allows the user to name their new table, as well as submit up to five new column names and a select box next to each for the user to pick the data type for that column they are adding.
  • for t_table_name_current in list_tables: Loop through each item in the “list_tables” array (Python calls this a List), which was passed to this template. Also naming each item in the loop as “t_table_name_current”.
  • {{ t_url }}: We placed this script within divs to style the html, as mentioned above. Next come curly brackets. As you can see, two of them together tell the server to place values in that spot. In this case, we are filling that spot with the contents of the “t_url” variable and “t_table_name_current”. t_url is the URL for our Python application and t_table_name_current is the current for loop item from the list_tables array.
  • endfor: Normally we don’t need a “next” or “endfor” type statement to designate the end of a for loop in Python. Dyanmic HTML from render_template requires the “endfor” because we are not relying on indenting to tell Python when the loop ends.

Now that we have created a templae (dynamic HTML page) to be rendered from our Python application, save the file in a folder off the root of your server called “templates\”. Save the file as “table_manager.html” in that folder.

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 Example

Here are some examples of how the syntax above might look when used in a project.

t_do_next = request.args.get("t_do_next", "")
t_form_input = request.form.get("t_form_input", "")

Analysis

  • request.args.get: This is akin to “Examine the querystring buried in the URL, find the ‘?’. After that question mark symbol, find ‘t_do_next=’ and put the value that comes after that equal sign symbol into a variable called ‘t_do_next’.”
  • t_form_input: This line of code grabs the value the user put into the HTML form input field named “t_form_input” and places that value in the local Python variable called “t_form_input”.

Now that we have gone over the most complex parts, piece by piece, it’s time to examine the full Python application:

Source: Creating and deleting tables with Python

# -------------------------------
# Import frameworks needed
# -------------------------------
from flask import Flask
from flask import render_template # to render HTML template
from flask import request # get user input from forms and querystrings
import psycopg2 # set up Postgres database connection

# ---------------------
# Connect to database
# ---------------------
t_host = "Postgres database address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default port for postgreSQL 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"])

# --------------------------
# Delete one table
# --------------------------
def DeleteTable(t_table_name_current):
    # query to delete table
    s = ""
    s += "DROP TABLE IF EXISTS"
    s += " (%t_table_name_current)"
    # execute the SQL
    db_cursor.execute(s, [t_table_name_current])
    # if autocommit is off:
    db_conn.commit()

# ----------------------------------
# List Postgres tables
# ----------------------------------
def GetTableList():
    s = ""
    s += "SELECT"
    s += " table_name"
    s += " FROM information_schema.tables"
    s += " WHERE"
    s += " ("
    s += " table_schema = 'public'"
    s += " )"
    s += " ORDER BY 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_do_next = request.args.get("t_do_next", "")

    # if delete a table
    if t_do_next == "DeleteTable":
        t_table_name_current = request.args.get("t_table_name_current", "")
        return DeleteTable(t_table_name_current)

    if t_do_next == "TableCreate":
        # get name of new table
        t_name_table = request.form.get("t_name_table")
        # start building SQL for table creation
        s = ""
        s += "CREATE TABLE " + t_name_table + " ("
        s += "id serial NOT NULL"
        # cycle through 0 to 5
        for x in range(6):
            t_name_field = request.form.get("t_name_field_" + x, "")
            t_data_type = request.form.get("t_data_type_" + x, "")
            # only if the user filled in a field do we include
            #    that column in the SQL we are building
            if t_name_field != "" and t_data_type != "":
                s += ", " + t_name_field + " " + t_data_type

        # final parts of our query to create a table; give it a
        #    primary key that autoincrements
        s += ", CONSTRAINT tbl_" + t_name_table + "_pkey PRIMARY KEY (id)"
        s += ");"
        s += "CREATE UNIQUE INDEX " + t_name_table + "_id_idx ON " + t_name_table + " USING btree (id);"
        # execute the query to create the new table
        db_cursor.execute(s)

    # Retrieve list of tables into list_tables array
    list_tables = GetTableList()
    # Show the user our dynamic HTML template
    t_url = "[URL to application]" # be sure to fill this in!
    return render_template("table_manager.html", list_tables = list_tables, t_url = t_url)

Conclusion

In this article we learned to build an application allowing users to create and delete Postgres tables in Python. We created a web application in Python to display all public tables in the database, along with the ability for the user to delete and add new tables, along with whatever column names and types they choose. We began by setting up a database connection to PostgreSQL using the psycopg2 framework. We cycled through and displayed a list of tables in a dynamic HTML template page for the user to interact with using various features from both Python and Postgres, including render_template, Python List (array), for loop, drop table SQL command, and others. 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.