2. Flask with database

2.1. Introduction

In this chapter, we will use the Flask with database. Please understand the basic concepts of Flask in Chapter 1.

In this chapter, we will store some questions in the database; and then ask those questions to the user.

2.2. Basic setup

2.2.1. Files

  • The file ‘my_app.py’ is the top level application.
# my_app.py

from flask import Flask

app = Flask(__name__) # application 'app' is object of class 'Flask'

# import files
from routes import *


if __name__ == '__main__':
    # '0.0.0.0' = 127.0.0.1 i.e. localhost
    # port = 5000 : we can modify it for localhost
    app.run(host='0.0.0.0', port=5000, debug=True) # local webserver : app.run()
  • The file ‘routes.py’ stores all the ‘routing-related’ codes,

Note

This is complete code but data is not saved in the database. All the elements of this code are discussed in Chapter 1. The remaining database-related codes are as follows,

  • Store ‘questions’ in the database (Lines 23-24).
  • Read question from the database (Lines 34-35).
  • Read answer from the database to compare with submitted-answer (Lines 42-43).
 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
# routes.py

from my_app import app
from flask import render_template, request

# home page
@app.route('/')  # root : main page
def index():
    # by default, 'render_template' looks inside the folder 'template'
    return render_template('index.html')

# Create question
@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'GET':
        # send the form
        return render_template('create.html')
    else: # request.method == 'POST':
        # read data from the form and save in variable
        question = request.form['question']
        answer = request.form['answer']

        # store in database
        # add code here

        return render_template('createThanks.html', question=question)


# Display question
@app.route('/question/<int:id>', methods=['GET', 'POST'])
def question(id):
    if request.method == 'GET':
        # send the form
        # add code here to read the question from database
        question = "Not added yet"

        return render_template('question.html', question=question)
    else: # request.method == 'POST':
        # read and check answers
        submitted_answer = request.form['answer']

        # add code here to read the answer from database
        correct_answer = "Not added"

        if submitted_answer == correct_answer:
            return render_template('correct.html');
        else:
            return render_template('sorry.html',
                answer = correct_answer,
                yourAnswer = submitted_answer
            )
  • The ‘index.html’ files is the main page. Note that ‘id=1 (instead of id=0)’ is used here as the SQLite-database index starts from ‘1’.
<!-- index.html -->

<!DOCTYPE html>
<html>
<head>
    <title>Home</title>
</head>
<body>
    <h1> Home Page </h1>

    <p><a href="{{url_for('create')}}">Create Question</a></p>
    <p><a href="{{url_for('question', id=1)}}">Answer Question</a></p>
</body>
</html>
  • The ‘create.html’ will store the form to ‘create’ and ‘save’ questions in the database.
<!-- create.html -->

<!DOCTYPE html>
<html>
<head>
    <title>Create</title>
</head>
<body>
    <h1> Create question </h1>

    <form method='post'>

            <b>Question</b> <input type="text" name="question"><br>
            <b>Answer</b> <input type="text" name="answer"><br>

            <input type="submit" name="submit" value="Submit">
            <input type="reset" name="reset" value="Reset"><br>
    </form>

    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>
  • The ‘question.html’ file will present the question to the users,
<!-- question.html -->

<!DOCTYPE html>
<html>
<head>
    <title>Question</title>
</head>
<body>
    <h1> {{ question }} </h1>

    <form method='post'>
        <b>Answer</b> <input type="text" name="answer"><br>
        <input type="submit" name="submit" value="Submit">
        <input type="reset" name="reset" value="Reset"><br>
    </form>

    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>
  • The ‘correct.html’ page will be displayed if the answer is correct,
<!-- correct.html  -->

<!DOCTYPE html>
<html>
<head>
    <title>Congratulation</title>
</head>
<body>
    <h2> Congratulation! Your answer is correct. </h2>

    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>
  • The ‘sorry.html’ will be displayed for incorrect answer,
<!-- sorry.html  -->

<!DOCTYPE html>
<html>
<head>
    <title>Sorry</title>
</head>
<body>
    <h2> Sorry! Your answer is incorrect.</h2>
    <p> The correct answer is : <b> {{ answer }} </b> </p>
    <p> You submitted : <b> {{ yourAnswer }} </b> </p>

    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>
  • The ‘createThanks.html’ will be displayed for after creating the question.
<!-- createThanks.html  -->

<!DOCTYPE html>
<html>
<head>
    <title>Thanks</title>
</head>
<body>
    <h1> Thank You! Your question is submitted successfully. </h1>
    <p> Your question was : <b> {{ question }} </b> </p>

    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>

2.2.2. Check setup

Now run the project using following command,

$ python my_app.py

Go to following links and check the output,

 Home Page

Create Question

Answer Question
Create question

    Question
    Answer

    Submit  Reset

    Go to main page
Not added yet

Answer .........

Go to main page
Congratulation! Your answer is correct.

Go to main page

2.3. Create database and save data

We will use the SQLite database in this tutorial. Please see the MySQL tutorials to use the MySQL database. Further, the process of connecting to database using Python is discussed there.

Modify the ‘routes.py’ as below,

  • Lines 8-11 create a database ‘qa_database.db (Line 9)’ and adds a table ‘tbl_QA (Line 10)’ to it. Note that, a new database will be creade if it does not exist; otherwise it will not create the new database.
  • Lines 30-44 saves the form-data (Lines 27-28) to the database (Lines 35-36). Read comments for more details.
  • The ‘database_error.html’ is used at Line 42 which shows the database-connection-error, if exists.

Note

  • In SQLite, the primary key (i.e. ‘ID’ at Line 9) starts with ‘1’ (not with 0).
  • Database queries are case-insensitive i.e ‘id’ and ‘ID’ are same thing.
  • We can use the firefox-addon ‘SQLite Manager’, which is quite handy tool to see the data in SQLite database.
 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
# routes.py
import sqlite3 as sql

from my_app import app
from flask import render_template, request

# connect to qa_database.sq (database will be created, if not exist)
con = sql.connect('qa_database.db')
con.execute('CREATE TABLE IF NOT EXISTS tbl_QA (ID INTEGER PRIMARY KEY AUTOINCREMENT,'
            + 'question TEXT, answer TEXT)')
con.close

# home page
@app.route('/')  # root : main page
def index():
    # by default, 'render_template' looks inside the folder 'template'
    return render_template('index.html')

# Create question
@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'GET':
        # send the form
        return render_template('create.html')
    else: # request.method == 'POST':
        # read data from the form and save in variable
        question = request.form['question']
        answer = request.form['answer']

        # store in database
        try:
            con = sql.connect('qa_database.db')
            c =  con.cursor() # cursor
            # insert data
            c.execute("INSERT INTO tbl_QA (question, answer) VALUES (?,?)",
                (question, answer))
            con.commit() # apply changes
            # go to thanks page
            return render_template('createThanks.html', question=question)
        except con.Error as err: # if error
            # then display the error in 'database_error.html' page
            return render_template('database_error.html', error=err)
        finally:
            con.close() # close the connection


# Display question
@app.route('/question/<id>', methods=['GET', 'POST'])
def question(id):
    if request.method == 'GET':
        # send the form
        # add code here to read the question from database
        question = "Not added yet"

        return render_template('question.html', question=question)
    else: # request.method == 'POST':
        # read and check answers
        submitted_answer = request.form['answer']

        # add code here to read the answer from database
        correct_answer = "Not added"

        if submitted_answer == correct_answer:
            return render_template('correct.html');
        else:
            return render_template('sorry.html',
                answer = correct_answer,
                yourAnswer = submitted_answer
            )
  • Below is the content of file ‘database_error.html’ to display the database connection errors,
<!-- database_error.html  -->

<!DOCTYPE html>
<html>
<head>
    <title>Database</title>
</head>
<body>
    <h2> Error in database connection </h2>

    <p> Error : <b>{{ error }}</b> </p>
    <p>Go to <a href="{{url_for('index')}}">main page</a></p>
</body>
</html>
Question : 2+2
Answer = 4

Add some more question like this.

2.4. Read the data from database

Now, we will add the code display the question (based on ID) by reading the database; and reading the ‘answer’ from the database to compare it with submitted answer.

  • Lines 54-68 reads the question from the database.
  • Lines 75-88 reads the answer from the database.
  • Read comments for further details.
 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
# routes.py

import sqlite3 as sql

from my_app import app
from flask import render_template, request

# connect to qa_database.sq (database will be created, if not exist)
con = sql.connect('qa_database.db')
con.execute('CREATE TABLE IF NOT EXISTS tbl_QA (ID INTEGER PRIMARY KEY AUTOINCREMENT,'
            + 'question TEXT, answer TEXT)')
con.close

# home page
@app.route('/')  # root : main page
def index():
    # by default, 'render_template' looks inside the folder 'template'
    return render_template('index.html')

# Create question
@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'GET':
        # send the form
        return render_template('create.html')
    else: # request.method == 'POST':
        # read data from the form and save in variable
        question = request.form['question']
        answer = request.form['answer']

        # store in database
        try:
            con = sql.connect('qa_database.db')
            c =  con.cursor() # cursor
            # insert data
            c.execute("INSERT INTO tbl_QA (question, answer) VALUES (?,?)",
                (question, answer))
            con.commit() # apply changes
            # go to thanks page
            return render_template('createThanks.html', question=question)
        except con.Error as err: # if error
            # then display the error in 'database_error.html' page
            return render_template('database_error.html', error=err)
        finally:
            con.close() # close the connection


# Display question
@app.route('/question/<int:id>', methods=['GET', 'POST'])
def question(id):
    if request.method == 'GET':
        # send the form
        # code to read the question from database
        try:
            con = sql.connect('qa_database.db')
            c =  con.cursor() # cursor
            # read question : SQLite index start from 1 (see index.html)
            query = "Select question FROM tbl_QA where id = {0}".format(id)
            c.execute(query)
            question = c.fetchone() # fetch the data from cursor
            con.commit() # apply changes
            # go to thanks page : pass the value of tuple using question[0]
            return render_template('question.html', question=question[0])
        except con.Error as err: # if error
            # then display the error in 'database_error.html' page
            return render_template('database_error.html', error=err)
        finally:
            con.close() # close the connection

        return render_template('question.html', question=question)
    else: # request.method == 'POST':
        # read and check answers
        submitted_answer = request.form['answer']

        # code to read the answer from database
        try:
            con = sql.connect('qa_database.db')
            c =  con.cursor() # cursor
            # read answer : SQLite index start from 1 (see index.html)
            query = "Select answer FROM tbl_QA where id = {0}".format(id)
            c.execute(query)
            correct_answer = c.fetchone()[0] # fetch and store tuple-value (see [0])
            con.commit() # apply changes
        except con.Error as err: # if error
            # then display the error in 'database_error.html' page
            return render_template('database_error.html', error=err)
        finally:
            con.close() # close the connection

        if submitted_answer == correct_answer:
            return render_template('correct.html');
        else:
            return render_template('sorry.html',
                answer = correct_answer,
                yourAnswer = submitted_answer
            )
  • Now, go to question link e.g. http://0.0.0.0:5001/question/0 and answer the question.
  • If answer is correct then we will get message from ‘correct.html’ as below,
Congratulation! Your answer is correct.

Go to main page
  • For incorrect answer, following message will be displayed through ‘sorry.html’
Sorry! Your answer is incorrect.

The correct answer is : 4

You submitted : 5

Go to main page

Note

We did not add the app.route for ‘correct.html’ and ‘sorry.html’, therefore the URL will not change after submitting the answers as shown in Fig. 2.1

../_images/flask2_1.png

Fig. 2.1 URL after submitting the answer