Managing Tables with TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this instructional article we’ll learn to create an interface to provide a way for managing tables with TimescaleDB and Python. we’ll build an application in Python to show a list of tables to the user and and offer them the opportunity to delete any of the tables via a click or tap. We will begin with setting up a TimescaleDB database connection in with Python using the psycopg2 framework. We’ll then use Python and SQL to cycle through and display a list of tables in a dynamic HTML web page for the user to interact with via Python’s render_template function from the Flask framework. Some other methods, commands, etc. we will be using include:

  • Python List to store all rows placed into a database cursor from fetchall execution.
  • for loop to loop through all tables found in our database.
  • Drop table to delete a TimescaleDB table based on user input.
  • div HTML tag for building the Python template.

The first thing to do is set up a database instance. We prefer using Objectrocket because we like their value and efficiency.

Create a TimescaleDB Database Instance on ObjectRocket

(1) Go to kb.objectrocket.com and “Log In”. (2) Use left menu to “Create Instance”. (3) In the “Name” field, add a descriptive name for your instance. (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”. (5) Choose the “Cloud Provider” and “Type” you want. (6) Select “Version” and “Region”. (7) Click the “GO TO STEP 2” button. (8) Make a choice in the “Pick Your Flavor” section. (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right. (10) Click the “GO TO STEP 3” button. (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”. (12) Choose between “Master” and “Replica” and click the “ADD” button. (13) Now click the “CREATE INSTANCE” button at the bottom.

The next item to get out of the way is to create a database connection to TimescaleDB using the psycopg2 library for Python. Pyscopg2 was initially built for managing TimescaleDB, but TimescaleDB’s creators chose to make their database system highly compatible with Postgres, which has shown Python developers an easy path for managing TimescaleDB databases using the psycopg2 library.

TimescaleDB Connection With Python

1
2
3
4
5
6
7
8
9
10
import psycopg2
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

Once we have a connection to TimescaleDB, we will write a bit of SQL code to pull a list of public tables in the database.

Query to Retrieve Table List

Here’s a bit of SQL code to pull a list of tables from TimescaleDB that are in the “public” schema.

1
2
3
4
5
6
7
8
9
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_name;"

Analysis

  • table_schema = ‘public’: This determines that we want only “public” tables returned, rather than system tables.
  • table_name: The system designated names of tables in our database schema.

Now to execute on data retrieval using the SQL we wrote above.

Table Names Into List

Now that we used Python to build the TimescaleDB SQL for pulling a table list, we’ll execute the query and use a for loop to cycle through all the tables returned. Note, the following Python code will differ some in our final code because we’ll pass the entire list (array) over to the dynamic HTML web page we build below.

1
2
3
4
data_cursor.execute(s)
listTables = data_cursor.fetchall()
for txtTableName in listTables:
    print(txtTableName + "\n")

Analysis

  • listTables = data_cursor.fetchall(): The psycopg2 fetchall function returns all rows returned from a query sent to TimescaleDB. This row set is kept in the Python list we named “listTables”.
  • for txtTableName: This is the heart of our for loop, assigning the “txtTableName” variable to track each iteration of a value kept in the “list_tables” list.
  • print: Returns the name of the table each time through the for loop, adding “n”, to send a carriage return. In our final application, we will leave this out because we will place this loop in the dynamic HTML web page we’ll build next.

Create a Dynamic HTML Web Page

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<html>
<head>
<title>Manage TimescaleDB Tables</title>
<style>
body {background-color: #E2E2E2;}
h1 {font-size: 34pt;}
.div-tbl {margin: auto; padding-left: 15px; padding-right: 15px;}
.div-record {margins: 4px; padding: 4px;}
.div-column {margin: auto;}
</style>
</head>
<body>
<div class='div_tbl'>
    <!-- headings record -->
    <div class='div_record'>
        <div class='div-column'>Table Name</div>
        <div class='div-column'>Remove</div>
    </div>
</div>
{%
for txtRecordCurrent in listTables:
    txtTableName = txtRecordCurrent[1]
%}
    <div class='div_record'>
        <div class='div-column'>{{ txtTableName }}</div>
        <div class='div-column'><a href='{{ txtURLapp }}?txtActionRoute=delete&txtTableName={{ txtTableName }}'> X </a></div>
    </div>
{%
endfor
%}
</div>
</body>
</html>

Analysis

  • div: Used for encapsulation in HTML script so that styles, javascript, etc. can be applied to content. We left out the css here necessary to lay the div contents out in a table-like format.
  • for txtTableName in listTables: Iterate through every item in the “listTables” list, which was passed on to this page. Also naming each item in the loop as “txtTableName” so it can be referrenced within the HTML below.
  • {{ txtURLapp }}: 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’re filling that spot with the contents of “txtURLapp” and “txtTableName”. txtURLapp is the URL for our application and txtTableName is the current for loop item from the listTables list.
  • endfor: With Python we usually don’t require a “next” or “endfor” type statement to determine the “bottom” of a for loop. Dyanmic HTML from a Flask template needs this explicit end point because we’re not relying on indenting to tell Python when the loop ends. Why? Web clients like Firefox, Chrome, Safari, etc. ignore two consecutive spaces.

Now that we have created a dynamic HTML web page that will be brought up 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 “manage_public_tables.html” in that folder.

Now that we have gone through the process for TimescaleDB and Python to show all tables, we will examine how we receive data from the user on the server-generated HTML page we developed above and route within our Python application accordingly. Note: this application is limited to to merely deleting tables. An idea for future expansion would be to allow the user to edit and create tables.

Request Data From HTML

To get started, we will increase 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 – pulling data that was submitted via an HTML form.

Python Request Syntax

1
2
3
4
# Querystring Using GET.
txtValue_from_retrieve = request.args.get([name of parameter], [value if a null value is received])
# Form Submission Using POST.
txtValue_from_post = request.form.get(name of parameter, [value if a null value is received])

Analysis

  • name of parameter: This is the name of the field to get. For example, “txtActionRoute” in our Python TimescaleDB application.
  • value if a null value is received: In case no values were sent from the form, this is the value your txtValue_from_retrieve variable will receive. This is a way to trap errors. We often put an empty string here such as “”, as you’ll see in the final code listing at the bottom of this tutorial document.

Python Request Example

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

1
2
txtActionRoute = request.args.get("txtActionRoute", "")
txtActionRoute = request.form.get("txtActionRoute", "")

Analysis

  • txtActionRoute: This piece of the Python script is like asking the server, “Examine the querystring contained in the URL, find the ‘?’. After that question mark symbol, look for ‘txtActionRoute=’ and put the value that comes after that equal sign symbol into a local variable we named txtActionRoute.”

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
# -------------------------
# Import Required Libraries
# -------------------------
from flask import Flask
from flask import render_template # For rendering the dynamic HTML template.
from flask import request # To get user inputs.
import psycopg2 # For TimescaleDB database connection.

# ------------------------
# Connect to TimescaleDB
# ------------------------
import psycopg2
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

# -------------
# Set App Route
# -------------
@app.route("/Main", methods=["GET", "POST"])

# -----------------------
# Delete a Db Table
# -----------------------
def DeleteTable(txtTableName):
    # SQL for deleting table.
    s = ""
    s += "DROP TABLE IF EXISTS "
    s += "'" + txtTableName + "'"
    try:
        data_cursor.execute(s)
    except psycopg2.Error as e:
        txtErrMsg = "SQL error: " + e + "/n SQL: " + s
        return render_template("errorpage.html", msg = txtErrMsg)
    # Need this if autocommit is not on.
    data_connection.commit()

# ----------------------------------
# Retrieve List of Public 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;"
    try:
        data_cursor.execute(s)
    except psycopg2.Error as e:
        txtErrMsg = "SQL error: " + e + "/n SQL: " + s
        return render_template("errorpage.html", msg = txtErrMsg)
    # Pull all the records into the cursor
    return data_cursor.fetchall()
    data_cursor.close()

# -------------------
# Primary Application
# -------------------
def Main():
    # Check querystring for user action
    txtActionRoute = request.args.get("txtActionRoute", "")
    txtTableName = request.args.get("txtTableName", "")
    # If coming from user then delete a table
    if txtActionRoute != "" AND txtTableName != "":
        return DeleteTable(txtTableName)
    # Either way: get table list and call the dynamic HTML template.
    listTables = GetTableList()
    # Show the HTML template
    txtURLapp = "[URL to this Python app]"
    return render_template("manage_public_tables.html", listTables = listTables, txtURLapp = txtURLapp)

Conclusion

In this instructional article, your knowledge grew about how to use TimescaleDB for managing tables with Python. We developed a Python application to display a list of TimescaleDB tables to the user and and offer them the ability to delete any of the public tables. We saw how to set up a TimescaleDB database connection with Python using the psycopg2 library; used Python, SQL, and a FOR loop to go through and display each table on a dynamic HTML web page for the user to interact with via the Python Flask render_template() function. 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.