Using Python Flask with Postgres

Written by Data Pilot

December 12, 2019

Flask
PostgreSQL
Python

Introduction

In this article, we will study using Python Flask with Postgres. We’ll first learn what Flask is and how it came to be. Next, we’ll study how to use three distinct Flask functionalities (there are more). Here is a brief explanation of what these three Python Flask functions are, at a high level:

Render_template: This function allows us to display to the user a dynamic web page they can interact with. We can send data to be dyanmically shown on that page via parameters.
Request: From Python in some apps it is imperative to retrieve data from a form or querystring. We use request from Flask to do this.
Mail: Useful for sending email from your Python application using the SMTP protocol.

We’ll create an application for user sign in and password retrieval using Python Flask with PostgreSQL and the functions above so as to use a real world situation to learn by.

Prerequisites

Novice-level understanding of how programming languages work in general.
Helpful if you have a beginner’s understanding of Python.
Even better if you have written any Python applications.
More helpful if you have some exposure to Postgres and/or SQL.

Python Flask

From a high level, Flask and Django are similar in that they are both web frameworks for Python. Flask was developed after Django, and Flask’s creator, Armin Ronacher, learned from how the Python community reacted to and utilized Django. In most situations, Flask is more explicit and easier to use for beginners than Django. There are many free and paid tutorials and courses available for those who want to learn Flask. Flask provides so many added functionalities for Python that we are going to focus on learning three. That said, here is a list of some of the more commonly used Flask features:

Redirect method.
Make_response method.
File object and save method.
Markup class.
Session objects.
Url_for function.
And much more!

Let’s begin our journey with the first of the three Python Flask functionalities we will study here:

Render template

We use the render_template function to display a web page we have prepared in advance, usually dyanmic. Here, by “dynamic,” we mean a page that we can send variables to via parameters sent through the render_template function. These dynamic pages are called templates and can even incorporate Python scripting in between chunks of HTML and even Javascript. Do not confuse render_template with a similar function, redirect, which sends the user to a different sub application.

Render_template prerequisites

You will want to install the following Flask framework and libraries so you can use render_template in your Python code:

1 2	from flask import Flask from flask import render_template

Flask Render template syntax

1	RETURN render_template("templates/template_file_name.html", parameter = "value")

Render_template example

We provide RENDER_TEMPLATE with the following parameter:

1	RETURN render_template(t_path_n_url, t_msg = t_msg)

t_msg: The contents of t_msg will be sent along with the user to the template at t_path_n_url, an html template you build, that is set up to receive and use t_msg as we’ll show you a bit later.

Note for comparison:

REDIRECT() syntax

1	RETURN redirect(url)

Python template example

1
2
3
4
5
6
7
8
9

<div class='div_outer'>
{%
for t_name_feeling in array_feelings:
%}
<div class='div_inner'>feeling: {{ t_name_feeling }}</div>
{%
endfor
%}
</div>

Analysis

for t_name_feeling in array_feelings: Iterate every item in the “array_feelings” array (called “list” in Python), which was passed to this template. Also naming each item in the loop as “t_name_feeling”.
div: Used in HTML for encapsulation so that styles, javascript, and other treatments can be done to the content inside the div and close div tages.
feeling: {{ t_name_feeling }}: First, we encapsulate the line in divs. This is for a few reasons, including (a) provide ability to style the output; and (b) provide each line of output with a carriage return. Next are those curly brackets. As you can see, two of them together tell the server to look for some value to place in that spot. In this case, we are filling that spot with the contents of “t_name_feeling”.
endfor: Flask templates need this because we are no longer relying on indent to specify when the loop ends. We need it here so that it is apparent to the server that this is the end of our for loop.

Python Flask Mail

Flask Mail syntax

First we need to import the correct Flask libraries to use the various mail-related commands.

1
2
3

from flask import Flask
from flask_mail import Mail
from flask_mail import Message

Now we need to set up the configuration we’ll later use to send out a mail message.

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

with open(config_name) as f:
config_data = json.load(f)
if smtp not in {1,2}:
raise ValueError("smtp can only be 1 or 2")
if smtp == 2:
MAIL_USERNAME = config_data['MAIL_USERNAME'][1]
MAIL_PASSWORD = config_data['MAIL_PASSWORD'][1]
else:
MAIL_USERNAME = config_data['MAIL_USERNAME'][0]
MAIL_PASSWORD = config_data['MAIL_PASSWORD'][0]
MAIL_SERVER = config_data['MAIL_SERVER']
MAIL_PORT = config_data['MAIL_PORT']
MAIL_USE_TLS = bool(config_data['MAIL_USE_TLS'])

mail = Mail()

smtp_data = smtp_config('config.json', smtp=1)
app.config.update(dict(
MAIL_SERVER = smtp_data[2],
MAIL_PORT = smtp_data[3],
MAIL_USE_TLS = smtp_data[4],
MAIL_USERNAME = smtp_data[0],
MAIL_PASSWORD = smtp_data[1],
))
mail.init_app(app)

Finally, the code for sending a mail message.

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

t_topic = "Your PW reset link"
t_from = "admin@bozo.com"

s = ""
s += "To " + t_email + " "
s += " "
s += "Thank you for using bozo.com!" + " "
s += " "
s += "Click on the link below or paste it into your web browser's URL bar:" + " "
s += " "
s += "<a href='http://bozo.com/reset_pw.py?forgot=step2&ID_user=" + ID_user
s += "'>https://bozo.com/reset_pw.py?forgot=step2&ID_user=" + ID_user
s += "</a>" + " "
s += " "
s += "If you have any questions, feel free to reply to this message." + " "
s += " "

t_mess = Message(
body = s,
subject = t_topic,
recipients = t_email,
sender = t_from,
reply_to = t_from,
)

mail.send(t_mess)

Flask Request

Python Import for request

1
2
3

from flask import Flask
from flask import render_template # to render the html form
from flask import request # to get user input from sign-in form

Request syntax

1 2	x = request.args.get(param) y = request.form.get(param, "")

Analysis

request.args.get: This retrieves data from the querystring; GET.
request.form.get: This retrieves data submitted from a form; POST. The two quotes means “If NULL value retrieved then pass this value.”

Flask Request example

1 2	t_branch = request.args.get("forgot") t_email = request.form.get("t_email", "")

Now that we have examined and learned about three important Flask functions for Python, we’ll put them together with some reads and writes from Postgres in order to create an application.

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208

from flask import Flask
from flask import render_template # to render the html form
from flask import request # to get user input from sign-in form
import hashlib # included in Python library, no need to install
import psycopg2 # for database connection
from flask_mail import Mail
from flask_mail import Message

app = Flask(__name__)

# Mail setup
def smtp_config(config_name, smtp=1):
with open(config_name) as f:
config_data = json.load(f)
if smtp not in {1,2}:
raise ValueError("smtp can only be 1 or 2")
if smtp == 2:
MAIL_USERNAME = config_data['MAIL_USERNAME'][1]
MAIL_PASSWORD = config_data['MAIL_PASSWORD'][1]
else:
MAIL_USERNAME = config_data['MAIL_USERNAME'][0]
MAIL_PASSWORD = config_data['MAIL_PASSWORD'][0]
MAIL_SERVER = config_data['MAIL_SERVER']
MAIL_PORT = config_data['MAIL_PORT']
MAIL_USE_TLS = bool(config_data['MAIL_USE_TLS'])
return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]

mail = Mail()

smtp_data = smtp_config('config.json', smtp=1)
app.config.update(dict(
MAIL_SERVER = smtp_data[2],
MAIL_PORT = smtp_data[3],
MAIL_USE_TLS = smtp_data[4],
MAIL_USERNAME = smtp_data[0],
MAIL_PASSWORD = smtp_data[1],
))
mail.init_app(app)

# Database connection setup
t_host = "database address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "database user 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("/")

def showForm():
# Show our html template form to the user.
t_msg = "Login Application"
return render_template("login.html", message = t_msg)

@app.route("/login", methods=["POST","GET"])
def login():
t_branch = request.args.get("forgot")
ID_user = request.args.get("ID_user")
t_email = request.form.get("t_email", "")
if t_branch == "login" OR t_branch == "reset":
t_pw = request.form.get("t_pw", "")

# Check if email box was not filled in.
if t_email == "":
if t_branch == "forgot":
t_msg = "Reset Password: You left your email empty"
else:
t_msg = "Login: You left your email empty"
# If empty, send user back, along with a message
return render_template("login.html", message = t_msg)

# Check for password box not filled in.
# Note we are checking the t_branch variable to see if they are signing in or they forgot their password
# If they forgot their password, we don't want their password here. We only want their email address
# so we can send them a link in the next part of this article.
# In both 1st stage and 3rd, we harvest password, so t_branch is "login" or "reset"
if (t_branch == "login" OR t_branch == "reset") AND t_pw == "":
t_msg = "Login: Please fill in your password"
# If empty, send user back, along with a message
return render_template("login.html", message = t_msg)

# In both 1st stage and 3rd, we harvest password, so t_branch is "login" or "reset"
if t_branch == "login" OR t_branch == "reset":
# Hash the password
t_hashed = hashlib.sha256(t_pw.encode())
t_pw = t_hashed.hexdigest()

# Get user ID from PostgreSQL tbl_people table
s = ""
s += "SELECT ID FROM tbl_people"
s += " WHERE"
s += " ("
s += " t_email ='" + t_email + "'"
if t_branch != "login":
s += " AND"
s += " t_pw = '" + t_pw + "'"
s += " AND"
s += " b_enabled = true"
s += " )"

db_cursor.execute(s)

# Here we catch and display any errors that occur
# while TRYing to commit the execute our SQL script.
try:
array_row = cur.fetchone()
except psycopg2.Error as e:
t_msg = "Database error: " + e + "/n SQL: " + s
return render_template("login.html", message = t_msg)

# Cleanup our database connections
db_cursor.close()
db_conn.close()

ID_user = array_row(0)

# If they have used the link in the email we sent them then t_branch is "reset"
if t_branch == "reset":
# UPDATE the database with new password
s = ""
s += "UPDATE tbl_people SET"
s += " t_pw = '" & t_pw & "'"
s += "WHERE"
s += "("
s += " ID=" + ID_user
s += ")"

# Catch and display errors
db_cursor.execute(s)
try:
db_conn.commit()
except psycopg2.Error as e:
t_msg = "Reset password: Database error: " + e + "/n SQL: " + s
return render_template("login.html", message = t_msg)
db_cursor.close()
# They got this far, meaning they were found in the database

# First stage. They have filled in username and password, so t_branch is "login"
if t_branch == "login":
# UPDATE the database with a logging of the date of the visit
s = ""
s += "UPDATE tbl_people SET"
s += " d_visit_last = '" & now() & "'"
s += "WHERE"
s += "("
s += " ID=" + ID_user
s += ")"
# IMPORTANT WARNING: this format allows for a user to try to insert
# potentially damaging code, commonly known as "SQL injection".
# In a later article we will show some methods for
# preventing this.

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

# Redirect user to the rest of your application
return redirect("http://your-URL-here", code=302)

# If they have tapped "Send me a password reset link" then t_branch is "forgot".
if t_branch == "forgot":
# Send email to the user
# Set up smtp (send mail) config.
t_topic = "Your PW reset link"
t_from = "admin@bozo.com"

# Build message body here
s = ""
s += "To " + t_email + " "
s += " "
s += "Thank you for using bozo.com!" + " "
s += " "
s += "Click on the link below or paste it into your web browser's URL bar:" + " "
s += " "
s += "<a href='http://bozo.com/reset_pw.py?forgot=step2&ID_user=" + ID_user
s += "'>https://bozo.com/reset_pw.py?forgot=step2&ID_user=" + ID_user
s += "</a>" + " "
s += " "
s += "If you have any questions, feel free to reply to this message." + " "
s += " "
# Set up the message
t_mess = Message(
body = s,
subject = t_topic,
recipients = t_email,
sender = t_from,
reply_to = t_from,
)

# Send the mail
mail.send(t_mess)

# Show user they are finished and ask them to check email.
t_msg = "Login: Password reset link was sent to your email address."
return render_template("login.html", t_msg = t_msg)

# If user has used the link in the email we sent them then t_branch is "reset"
if t_branch == "reset":
# Show user they are finished and they need to sign in.
t_msg = "Login: Your password has been reset."
return render_template("login.html", t_msg = t_msg)

Conclusion

We studied using Python Flask with Postgres and used what we learned to create a Python and PostgreSQL web database application for login and password reset. We first learned what Flask is. Next, we studied how to use three Flask features; Render_template, Request, and Mail.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started