Request Input for CockroachDB

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

Introduction

In this tutorial document we learn how to use Request Input for CockroachDB SQL to get form data from web pages and insert that data into a Cockroach database. Here’s how we will go about it:

  • What? What is the request function from Flask and it’s syntax?
  • How? We will set up our Python environment to use Request via Flask, use the render_template function to send the user to an HTML form, use Python Flask’s request function along with .get and .form to receive data the user submitted in that form, and we will then insert that data into a CockroachDB database.

Prerequisites

  • Some familiarity with how to write SQL in CockroachDB, using PG’s free dBeaver tool or other database admin tool. And/or writing applications with coding languages like PHP, Python, C#, Java, ASP.Net, VB.Net, Node.js, Ruby, etc. In this case, we have used Python.
  • Some writing of simple SQL commands, including INSERT.
  • Some experience with HTML forms. If not, that’s ok, you will learn here.

Before we get into the syntax and parameters used for Request, let’s briefly go over the basics of how the render_template function works:

Flask’s Render_Template function displays an HTML page for the user, which can be filled with dynamic content we control with parameters for the function. Note: Please don’t confuse it with the redirect function, which sends the user away without waiting for a return.

Flask RENDER_TEMPLATE syntax

1
RETURN render_template("name_of_web_web-page.html", message = txtMessage)

We supply the RENDER_TEMPLATE function with this parameter:

  • txtMessage: The contents of “txtMessage” will be sent, along with the user, to name_of_web_web-page.html, an HTML template you create, that is set up to receive and use txtMessage. Note: instead of “txtMessage”, you can call your variable anything you want. You can even send more variables. Look for our tutorial document that goes into detail on the many uses of this function.

Request syntax

We use Flask’s Request function to receive data from a form POST or GET. Put simply:

  • POST: This is for when the HTML form’s “method” property is set to “post”.
  • GET: This is for when the HTML form’s “method” property is set to “get”, which sends the data by adding it to the end of the URL using a “?” symbol. Note: a form is not needed for this to happen. “A href” and other kinds of redirects can use this method. In this instructional article, we will show examples of both methods.

Syntax of the Flask REQUEST function

1
2
myVariable = request.args.get(field name, default value) # SQL codestring (or method=GET)
myVariable = request.form.get(field name, default value) # form POST

Analysis

  • field name: This parameter is where you put the name of the field (how it was named in your SQL codestring or form).
  • default value: In case no data was sent for that field, this is the value your variable will receive. This is a method for preventing 404 errors.

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

Request example

1
2
3
txtStage = request.args.get("txtStage", "") # this is a GET
txtNameUser = request.form.get("txtNameUser", "") # this is a POST
txtPassword = request.form.get("txtPassword", "") # this is a POST

Analysis

  • txtStage: This line of Python scripts is like saying to the server, “Look at the URL, find the ‘?’, and after that symbol, look for ‘txtStage=’ and put the value that comes after that ‘=’ into a local variable we are naming txtStage.”
  • txtNameUser: This line of scripts grabs the value the user put into the HTML field named “txtNameUser” and places that value in the local Python text variable we called “txtNameUser”.

Python requirements

You will have to install and reference these Python flask libraries in order to use the request and render_template functions, as well as psycopg2 for the Cockroach database insert we will do a bit later:

1
2
3
4
from flask import Flask
from flask import render_template
from flask import request
import psycopg2

Build HTML template

Below is some barebones HTML; the minimum you will need. Later, you may wish to modularize the script by referencing a header, css file, and footer, but for now, this will work on all web browsers:

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
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>Cockroach Python Template</title>
    </head>
<body>
<!-- The txtMessage you see below is why we call this -->
<!-- page a template. It's what makes this "dynamic" -->
<!-- because the server fills in the -->
<!-- spot where you see {{txtMessage}} with data you -->
<!-- sent when you used the "render_template" function. -->
<h1>{{txtMessage}}</h1>
<!-- div to start container -->
<div>
    <!-- Set up the form to process user input -->
    <form id='frmTest' name='frmTest' action='' method='post' onsubmit='return true;'>
    <!-- input box for the user to enter their Email address -->
    <!-- Notice our input has a 'name' (parameter of the HTML input type) of 'txtEmailAddr'. -->
    <!-- Side note: if we had some javacode running on this page, it would reference -->
    <!-- this field by using the 'id' parameter -->
    <!-- This is how our request function chooses which data to retrieve. -->
    <div class="form-input">
      <label for="Email">Email address:</label>
      <input type="text" id="txtEmailAddr" name="txtEmailAddr">
    </div>
    <!-- button for user to submit the form -->
    <div>
      <input type="submit" id="btnSubmit_give_email" value='Submit Data'>
    </div>
    <!-- Close the form tag -->
    </form>
<!-- Close the container div tag -->
</div>
<!-- Close the body of the HTML template -->
</body>
</html>

Analysis

  • : Div is a way of encapsulating content in HTML documents. Often times we may use divs to assign styles to content via a CSS “style” tag or with a “class” tag pointing to some predefined and named CSS.
  • For the rest, use the comments we liberally placed in the HTML above to learn how to make your own template.

Now that you know how to build an HTML form, save that file in a folder on the server in your “[site root]/templates” folder. Call the page “html-exercise.html” or whatever you want.

From HTML to CockroachDB

Let’s start out by setting up access to a Cockroach database.

1
2
3
4
5
6
7
8
9
10
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "22251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

Analysis

cursorCRDB: We set up this object to point to our database. We will use it later to get data from that database.

The next step is to send the user to the HTML page we created earlier and retrieve the data submitted by the user.

1
2
3
4
5
6
7
8
9
@app.route("/exercise", methods=["POST","GET"])
def sign_in():
  return render_template("html-exercise.html", txtMessage = "Share your email address")
  txtEmailAddr = request.form.get("txtEmailAddr", "")
  # Check to see if the email field was left empty.
  if txtEmailAddr == "":
      txtMessage = "Login: Please fill in your email address"
      # Send user back, along with a message
      return render_template("html-exercise.html", message = txtMessage)

Finally, we will INSERT a new row into a table named “tblUsers” using Python and Cockroach SQL combined:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
s = ""
s += "INSERT INTO tblUsers "
s += "("
s += " txtEmailAddr"
s += ") VALUES ("
s += " '(%txtEmailAddr)'"
s += ")"
cursorCRDB.execute(s, [txtEmailAddr])
# Here we catch and display any errors that might occur
#   while TRYing to commit the execute our code.
try:
    connCRDB.commit()
except psycopg2.Error as e:
    txtMessage = "Database error: " + e + "/n SQL: " + s
    return render_template("html-exercise.html", txtMessage = txtMessage)
cursorCRDB.close()

Now that you know how it all works, step by step, we will combine all that we learned above and add some of the more administrative parts required for our Python application to insert data from a form into a Cockroach database table. Please make sure you are running at least a newish version of Python.

Python 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
from flask import Flask
from flask import render_template
from flask import request
import psycopg2 # for database connection

app = Flask(__name__)
@app.route("/")

txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

@app.route("/exercise", methods=["POST","GET"])
def sign_in():
  return render_template("html-exercise.html", message = "Share your email address")
  txtEmailAddr = request.form.get("txtEmailAddr", "")

  # Check for email field left empty
  if txtEmailAddr == "":
      txtMessage = "Login: Please fill in your email address"
      # Send user back, along with a message
      return render_template("html-exercise.html", message = txtMessage)

  # Build some SQL
  s = ""
  s += "INSERT INTO tblUsers "
  s += "("
  s += " txtEmailAddr"
  s += ") VALUES ("
  s += " '(%txtEmailAddr)'"
  s += ")"

  # Send the SQL we built to the database via the cursorCRDB object:
  cursorCRDB.execute(s, [txtEmailAddr])

  # Here we are catching and displaying any errors that occur
  #   while TRYing to commit the execute our SQL code.
  try:
      connCRDB.commit()
  except psycopg2.Error as e:
      txtMessage = "Database error: " + e + "/n SQL: " + s
      return render_template("html-exercise.html", txtMessage = txtMessage)

  # Clean up
  cursorCRDB.close()

Conclusion

In this tutorial document your knowledge grew about how to use the Flask Request and Cockroach SQL to acquire data posted from an HTML form and insert it as a new record in our CockroachDB table. We built a small application using Python, CockroachDB SQL with SQL, and HTML. We also learned how to use the powerful render_template Python function that is part of Flask. Finally, we listed all of the Python source scripts you have to use so you can modify this exercise for other purposes you may have.

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.