Managing Tables with CockroachDB and Python

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

Introduction

In this tutorial we will learn to build an interface to provide a way for managing tables with CockroachDB and Python. 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 CockroachDB 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:

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

The first thing to get done is to create a database connection to CockroachDB using the psycopg2 library for Python. Pyscopg2 was initially created for managing CockroachDB, but CockroachDB’s creators chose to make their database system highly compatible with CockroachDB, which has afforded Python developers an easy path for managing Cockroach databases using the psycopg2 library.

CockroachDB connection with Python

1
2
3
4
5
6
7
8
9
10
11
12
import psycopg2
t_dbname = "database name"
t_name_user = "database user name"
t_sslmode = "auto"
t_sslrootcert = 'mycerts/ca.crt'
t_sslkey = 'mycerts/client.maxroach.key'
t_sslcert = 'mycerts/client.maxroach.crt'
t_host = "localhost"
t_port = "26256"
# Note the connection string for CockroachDB differs slightly from the one for PostgreSQL.
data_connection = psycopg2.connect(database=t_dbname, user=t_name_user, sslmode=t_sslmode, sslrootcert=t_sslrootcert, sslkey=t_sslkey, sslcert=t_sslcert, host=t_host, port=t_port)
data_cursor = data_connection.cursor()

Once we have a connection to Cockroach, we’ll write some SQL to retrieve a list of public tables in our database.

Query to get table list

Here’s some SQL to retrieve a list of tables from CockroachDB 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 we want only “public” tables returned, rather than system tables.
  • table_name: The system designated names of tables in the 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 CockroachDB SQL for retrieving a table list, we will 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 will pass the entire list (array) over to the dynamic HTML page we build below.

1
2
3
4
5
data_cursor.execute(s)
list_of_tables = data_cursor.fetchall()

for t_tbl_name in list_of_tables:
    print(t_tbl_name + "\n")

Analysis

  • list_of_tables = data_cursor.fetchall(): The psycopg2 fetchall function returns all records returned from a query sent to CockroachDB. This record set is stored in the Python list we named “list_of_tables”.
  • for t_tbl_name: This is the meat of our for loop, assigning the “t_tbl_name” variable to track each iteration of a value stored 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’ll leave this out because we’ll place this loop in the dynamic HTML page we will build next.

Create a dynamic HTML 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 CockroachDB Tables</title>
<style>
body {background-color: #E0E0E0;}
h1 {font-size: 36pt;}
.div-tbl {margin: auto; padding-left: 14px; padding-right: 14px;}
.div-row {margins: 2px; padding: 3px;}
.div-col {margin: auto;}
</style>
</head>
<body>
<div class='div_tbl'>
    <!-- headings row -->
    <div class='div_row'>
        <div class='div-col'>Table Name</div>
        <div class='div-col'>Delete</div>
    </div>
</div>
{%
for t_row_current in list_of_tables:
    t_tbl_name = t_row_current[1]
%}
    <div class='div_row'>
        <div class='div_col'>{{ t_tbl_name }}</div>
        <div class='div_col'><a href='{{ t_url_app }}?t_action_route=delete&t_tbl_name={{ t_tbl_name }}'> 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 did not include the css here necessary to lay the div contents out in a table-like format.
  • for t_tbl_name in list_of_tables: Go through every item in the “list_of_tables” list, which was passed on to this page. Also naming each item in the loop as “t_tbl_name” so it can be referrenced within the HTML below.
  • {{ t_url_app }}: 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_app” and “t_tbl_name”. t_url_app is the URL for our application and t_tbl_name is the current for loop item from the list_of_tables list.
  • endfor: With Python we usually don’t need a “next” or “endfor” type statement to determine the “bottom” of a for loop. Dyanmic HTML from a Flask template requires this explicit end point because we are 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 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 “tables_public_manage.html” in that folder.

Now that we have gone through the process for CockroachDB and Python to list all tables, we’ll look at how we receive data from the user on the dynamic HTML page we built 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 start off, 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

1
2
3
4
# Querystring using GET.
t_value_from_get = request.args.get([name of parameter], [value if a null value is received])
# Form submission using POST.
t_value_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 retrieve. For example, “t_action_route” in our Python CockroachDB application.
  • value if a null value is received: In case no data was sent from the form, this is the value your t_value_from_get variable will receive. This is a way to trap errors. We often put an empty string here such as “”, as you will see in the final code listing at the bottom of this article.

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:

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

Analysis

  • t_action_route: 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_route=’ and put the value that comes after that equal sign symbol into a local variable we named t_action_route.”

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
81
# -------------------------
# Import required libraries
# -------------------------
from flask import Flask
from flask import render_template # For rendering the dynamic HTML template.
from flask import request # To retrieve user inputs.
import psycopg2 # For CockroachDB database connection.

# ------------------------
# Connect to CockroachDB
# ------------------------
import psycopg2
t_dbname = "Cockroach db name"
t_name_user = "Cockroach database user name"
t_sslmode = "auto"
t_sslrootcert = 'mycerts/ca.crt'
t_sslkey = 'mycerts/client.maxroach.key'
t_sslcert = 'mycerts/client.maxroach.crt'
t_host = "localhost"
t_port = "26256"
data_connection = psycopg2.connect(database=t_dbname, user=t_name_user, sslmode=t_sslmode, sslrootcert=t_sslrootcert, sslkey=t_sslkey, sslcert=t_sslcert, host=t_host, port=t_port)
data_cursor = data_connection.cursor()

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

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

# ----------------------------------
# Get 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:
        t_msg_err = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", msg = t_msg_err)
    # Retrieve all the rows into the cursor
    return data_cursor.fetchall()
    data_cursor.close()

# -------------------
# Primary application
# -------------------
def Main():
    # Check querystring for user action
    t_action_route = request.args.get("t_action_route", "")
    t_tbl_name = request.args.get("t_tbl_name", "")
    # If coming from user then delete a table
    if t_action_route != "" AND t_tbl_name != "":
        return DeleteTable(t_tbl_name)
    # Either way: get table list and call the dynamic HTML template.
    list_of_tables = GetTableList()
    # Show the HTML template
    t_url_app = "[URL to this Python application]"
    return render_template("tables_public_manage.html", list_of_tables = list_of_tables, t_url_app = t_url_app)

Conclusion

In this tutorial, we learned how to use CockroachDB for managing tables with Python. We built a Python application to display a list of CockroachDB tables to the user and and give them the ability to delete any of the public tables. We saw how to set up a CockroachDB database connection with Python using the psycopg2 library; used Python, SQL, and a FOR loop to iterate through and display each table on a dynamic HTML 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.