Python Request and Postgres SQL

Introduction

In this article we learn how to use Python Request and Postgres SQL to get form data from web pages and insert that data into a database. Here’s how we’ll do it:

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

Prerequisites

  • Some familiarity with how to write queries in PostgreSQL, using PG’s free PGadmin tool or other database admin tool. And/or writing applications with programming languages like PHP, Python, Javascript, C#, Java, ASP.Net, VB.Net, Note.js, Ruby, etc. In this case, we are using Python.
  • Some writing of simple SQL commands, including INSERT.
  • Some experience with HTML forms. If not, that’s ok, you will learn here.
  • Optional: Tutorial on naming conventions explaining why we prefix our variables, column names, table names, etc. as you see us doing in this article. For example, naming “tvariable_name” with the “t” you see at the beginning in order to delineate it as a “text” (string) object and “tbl_” before table names in order to clearly mark those objects as tables.

Before we get into the syntax and parameters used for request(), let’s quickly go over the basics of how the render_template function works:

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

RENDER_TEMPLATE() function syntax

RETURN render_template("page_name.html", message = t_message)

We supply the RENDER_TEMPLATE function with this parameter:

  • t_message: The contents of “t_message” will be sent, along with the user, to page_name.html, an html template you create, that is set up to receive and use t_message. Note: instead of “message”, you can call your variable anything you want. You can even send more variables. Look for our article that goes into detail on this function.

Now back to the topic at hand…

What does the REQUEST function do in Postgres and what is the syntax?

We use PostgreSQL’s Request() function to receive data from a form POST or GET (querystring). Put simply:

  • POST: This is where the HTML form’s “method” parameter is set to “post”.
  • GET: This is where the HTML form’s “method” parameter is set to “get”, which sends the data by tacking it on to the end of the URL with 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 tutorial, we’ll show examples of both methods.

Syntax of the REQUEST() function

variable = request.args.get(field name, default value) # querystring (or method=GET)
variable = 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 querystring or form).
  • default value: In case no data was sent for that field, this is the value your variable will receive. This is an easy way to prevent 404 errors.

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

t_stage = request.args.get("t_stage", "") # this is a GET
t_name_user = request.form.get("t_name_user", "") # this is a POST
t_password = request.form.get("t_password", "") # this is a POST

Analysis

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

SOME NECESSARY PYTHON CODE

You will need to install and reference these Python flask libraries in order to use the request and render_template functions, as well as the database insert we will do:

from flask import Flask
from flask import render_template
from flask import request
import psycopg2 # for database connection

Step 1: Build an HTML form that will be the “template” we call later

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

<html>
  <head>
    <link rel="shortcut icon" href="favicon.ico" />
    <title>Test Python for Postgres Template</title>
  </head>
  <body>
    <!-- The message 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 {{message}} with data you sent when you used -->
    <!-- the "render_template()" function. -->
    <h1>{{message}}</h1>
    <!-- div to contain form -->
    <div>
      <!-- Set up form and the file 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 't_email'. -->
        <!-- Side note: if we had some javascript 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-row">
          <label for="Email">Email address:</label>
          <input type="text" id="t_email" name="t_email" />
        </div>

        <!-- button for user to submit the form -->
        <div>
          <input type="submit" id="btn_submit_give_email" value="Submit Data" />
        </div>

        <!-- Close the form -->
      </form>
      <!-- Close the container div -->
    </div>
    <!-- Close the body of the HTML document -->
  </body>
</html>

Analysis

  • <div>: Div is a way of encapsulating content in HTML documents. Often times we use divs to assign styles to content via an inline “style” tag or with a “class” tag pointing to some predefined CSS.
  • For the rest, use the comments we liberally placed in the HTML above.

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

Build a test application: from HTML to Postgres

Let’s begin by setting up access to a Postgres database.

t_host = "PostgreSQL database host address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

Analysis

  • import psycopg2: This library supplies a method for connecting to, pulling data from, and sending data to a Postgres database. Today we will be doing an SQL INSERT of data the user submitted in the HTML form we built above.
  • db_cursor: We set up this object to point to our database. We’ll use it later to get data from that database.

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

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

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

Finally, we’ll INSERT a new record into a table named “tbl_users” using Python and Postgres SQL combined:

s = ""
s += "INSERT INTO tbl_users "
s += "("
s += " t_email"
s += ") VALUES ("
s += " '" + t_email + "'"
s += ")"
db_cursor.execute(s)

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

Now that you know how it all works, step by step, we’ll combine all that we learned and add some of the more administrative parts required for our Python application to work. NOTE: Please be sure you are running the latest version of Python.

Full Source Code in Python

from flask import Flask
from flask import render_template
from flask import request
import psycopg2 # for database connection

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

t_host = "PostgreSQL database host address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

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

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

  # Build some SQL
  s = ""
  s += "INSERT INTO tbl_users "
  s += "("
  s += " t_email"
  s += ") VALUES ("
  s += " '" + t_email + "'"
  s += ")"
  # IMPORTANT WARNING: this format allows for a user to try to insert
  #   potentially damaging code, commonly known as "SQL injection".
  #   In another article we show the most popular method for preventing
  #   this; Postgres stored procedures.

  # Send the SQL we built to the database via the db_cursor object:
  db_cursor.execute(s)

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

  # Clean up
  db_cursor.close()

Conclusion

In this article we learned how to use the Python Request and Postgres SQL to acquire data posted from an HTML form and insert it as a new row in our PostgreSQL table. We built a small application using Python, PostgreSQL queries with SQL, and HTML. We also learned how to use the very powerful render_template Python function that is part of Flask. Finally, we listed all of the Python source code you need to use and 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.