Edit Postgres Columns with Python

Introduction

In this article we’ll build an application to edit Postgres columns with Python. The functionality of this application includes prompting the user to pick a table from a list of Postgres tables, then being shown the columns in that table, along with the opportunity to remove any column or add a new column to the table. Following are some of the features of both Python and Postgres we will be using to build this application:

  • render_template: A function from the Flask library for Python.
  • Python List: Python’s version of an array.
  • for loop: Python loop structure we’ll use here to iterate through fields, columns, and tables.
  • Alter table: SQL command for altering a PostgreSQL table; in this case to use the “ADD COLUMN” SQL command.

We’ll build each of the primary parts of the demo app and then – at the end of the article – show the Python code for the entire app that allows picking a table from a PostgreSQL database and then editing columns in that table.

First we will write some Python script to retrieve table and column lists from the database.

List Postgres columns

    s = ""
    s += "SELECT"
    s += " column_name"
    s += " , data_type"
    s += " FROM information_schema.columns"
    s += " WHERE"
    s += " ("
    s += " table_name = '(%t_current_table)'"
    s += " )"
    s += " ORDER BY ordinal_position;"
    db_cursor.execute(s, [t_current_table])
    return db_cursor.fetchall()
    db_cursor.close()

Analysis

  • column_name: This is the reserved key word in Postgres for retrieving the names of the columns.
  • data_type: This is the Postgres reserved keyword for getting the data type of any given column. We won’t be showing this to the user but we encourage you to modify the dynamic HTML page we build below so that it does show this to the user. You’ll need to send this data to the dynamic HTML page via adding a parameter to the render_template function.
  • ordinal_position: This is the order of the current column within the current table.

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_name;"
db_cursor.execute(s)
return db_cursor.fetchall()
db_cursor.close()

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.

Display all tables with for loop

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

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

Analysis

  • for t_current_table: This defines the loop, creating the “t_current_table” variable to track each iteration of values in the “list_tables” array.

Now that we’ve retrieved a list of tables to loop through, we will build a dynamic HTML page to be called from the render_template function. The dynamic HTML page will display the table list that exists in list_tables and list_columns, which will be sent to the HTML page as parameters in the render_template function.

Finally, we will explore gathering data from the user via get and post from the HTML template and call different functions in our Python application based on that data from the user’s choices.

Build dynamic HTML page

<html>
<head>
<title>Creating and Deleting Columns for a Postgres Table</title>
{%
array_data_types = ["boolean", "integer", "real", "varchar"]
array_combo_boxes = ["", "", "", "", ""]
for x in range(5)
    array_combo_boxes[x] = "<select name='t_data_type_" + (x):text + "'>" + "\n"
    for t_data_type in array_data_types:
        array_combo_boxes[x] += "<option name='" + t_data_type + "'>" + t_data_type + "</option>" + "\n"
    endfor
    array_combo_boxes[x] += "</select>"  + "\n"
endfor
%}
<!-- array_data_types is a Python array we set up for -->
<!--    storing the four data types for our combo (select) boxes -->
<!-- array_combo_boxes is a Python list for storing the code for -->
<!--    those boxes, so each box can have a unique name -->
</head>
<body style='background-color: #DFDFDF;'>
<div class='div_app'>
    <form name='addColumns' action='{{ t_app_url }}?t_requested_action=ColumnAdd' method='post'>
    <div class='frm_input_caption'>Table Name</div>
    <div class='frm_box_text'><input name='t_current_table' type='text' value='{{ t_current_table }}'></div>
    <table style='border:1px;'>
    <tr>
    <td valign='top'>
        <!-- ADD COLUMNS -->
        <div class='heading'>Add Columns</div>
        <div class='frm_input_caption'>Column 1 Name</div>
            <div class='frm_box_text'><input name='t_current_column_0' type='text'></div>
            <div class='frm_box_text'>{{ array_combo_boxes[0] }} </div>
        <div class='frm_input_caption'>Column 2 Name</div>
            <div class='frm_box_text'><input name='t_current_column_1' type='text'></div>
            <div class='frm_box_text'>{{ array_combo_boxes[1] }} </div>
        <div class='frm_input_caption'>Column 3 Name</div>
            <div class='frm_box_text'><input name='t_current_column_2' type='text'></div>
            <div class='frm_box_text'>{{ array_combo_boxes[2] }} </div>
        <div class='frm_input_caption'>Column 4 Name</div>
            <div class='frm_box_text'><input name='t_current_column_3' type='text'></div>
            <div class='frm_box_text'>{{ array_combo_boxes[3] }} </div>
        <div class='frm_input_caption'>Column 5 Name</div>
            <div class='frm_box_text'><input name='t_current_column_4' type='text'></div>
            <div class='frm_box_text'>{{ array_combo_boxes[4] }} </div>
        <div></div><div><input name='go' type='submit' value='Add Columns'></div>
    </td>
    <td valign='top'>
        <!-- SHOW CURRENT COLUMNS -->
        <div class='heading'>Current Columns</div>
        {%
        for t_current_column in list_columns:
        %}
            <div class='div_row'>
                <div class='div_col'>{{ t_current_column }}</div>
                <div class='div_col'><a href='{{ t_app_url }}?t_requested_action=ColumnDelete&t_current_column={{ t_current_column }}'>
                {{ t_current_column }}</a></div>
            </div>
        {%
        endfor
        %}
    </td>
    </tr>
    </table>
</form>
</div>
<!-- SHOW CURRENT TABLES -->
<div class='heading'>Choose a Table for Column Editing</div>
<div class='div_tables'>
{%
for t_current_table in list_tables:
%}
    <div class='div_row'>
        <div class='div_col'>{{ t_current_table }}</div>
        <div class='div_col'><a href='{{ t_app_url }}?t_requested_action=TableChoose&t_current_table={{ t_current_table }}'>
        Pick {{ t_current_table }}</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_current_table 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_current_table”.
  • {{ t_app_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_app_url” variable and “t_current_table”. t_app_url is the URL for our Python application and t_current_table 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 “column_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_requested_action = request.args.get("t_requested_action", "")
t_frm_box_text = request.form.get("t_frm_box_text", "")

Analysis

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

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

# ---------------------------------------------
# Include Python framework/library dependencies
# ---------------------------------------------
from flask import Flask
from flask import render_template # for render_template
from flask import request # for getting user input from HTML page
import psycopg2 # for database operations

# -------------------
# Database connection
# -------------------
t_host = "Postgres database address" # "localhost", a domain name, or an IP address.
t_port = "5432" # default Postgres port
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"])

# ----------------------------
# Table deletion
# (unused in this application)
# ----------------------------
def DeleteTable(t_current_table):
    # query to delete table
    s = ""
    s += "DROP TABLE IF EXISTS"
    s += " (%t_current_table)"
    # execute the SQL
    db_cursor.execute(s, [t_current_table])
    # 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 rows
    db_cursor.execute(s)
    return db_cursor.fetchall()
    db_cursor.close()

# --------------------------
# List Columns from table
# --------------------------
def GetColumnList(t_current_table):
    s = ""
    s += "SELECT"
    s += " column_name"
    # -----------------
    # Note here is how you get data type
    #   but we are not listing this for the user
    #   in this application. It wouldn't be difficult
    #   for you to add this to the render_template parameters
    #   and HTML.
    s += " , data_type"
    # -----------------
    s += " FROM information_schema.columns"
    s += " WHERE"
    s += " ("
    s += " table_name = '(%t_current_table)'"
    s += " )"
    # ordinal_position is the order the column appears in the table
    s += " ORDER BY ordinal_position;"
    # Retrieve all the rows from the cursor
    db_cursor.execute(s, [t_current_table])
    return db_cursor.fetchall()
    db_cursor.close()

def DeleteColumn(t_current_table, t_current_column):
    # query to delete column
    s = ""
    s += "ALTER TABLE"
    s += " (%t_current_table)"
    s += " DROP COLUMN"
    s += " (%t_current_column)"
    # execute the query using parameters
    db_cursor.execute(s, [t_current_table], [t_current_column])
    db_conn.commit()

# ----------------------------
# Application main function
# ----------------------------
def Main():
    # check querystring for action to take
    t_requested_action = request.args.get("t_requested_action", "")

    # if select a table
    if t_requested_action == "TableChoose":
        t_current_table = request.args.get(t_current_table, "")
        return TableChoose(t_current_table)
        # Retrieve list of columns
        list_columns = GetColumnList(t_current_table)

    if t_requested_action == "ColumnDelete":
        return DeleteColumn(t_current_table, t_current_column)

    if t_requested_action == "ColumnAdd":
        # get name of table
        t_current_table = request.form.get("t_current_table")
        # start building SQL for table creation
        s = ""
        s += "ALTER TABLE " + t_current_table
        # cycle through 0 to 5
        for x in range(6):
            t_name_field = request.form.get("t_current_column_" + 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 != "":
                if x > 0:
                    s += ", "
                s += "ADD COLUMN " + t_name_field + " " + t_data_type

        # final parts of our query to add column(s); execute
        db_cursor.execute(s)

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

Conclusion

In this tutorial we created a Python application to edit Postgres columns. The functionality of this application includes allowing the user to pick a table from a list of Postgres tables, then being shown all columns in that table, along with the opportunity to remove any column or add a new column to any table picked by the user. Following are some of the features we used in building this application: render_template, for loop, Python array known as List, alter table, add column, and drop column. 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.