how to save form data to database in flask 5c

1# routes.py
2
3import sqlite3 as sql
4
5from my_app import app
6from flask import render_template, request
7
8# connect to qa_database.sq (database will be created, if not exist)
9con = sql.connect('qa_database.db')
10con.execute('CREATE TABLE IF NOT EXISTS tbl_QA (ID INTEGER PRIMARY KEY AUTOINCREMENT,'
11            + 'question TEXT, answer TEXT)')
12con.close
13
14# home page
15@app.route('/')  # root : main page
16def index():
17    # by default, 'render_template' looks inside the folder 'template'
18    return render_template('index.html')
19
20# Create question
21@app.route('/create', methods=['GET', 'POST'])
22def create():
23    if request.method == 'GET':
24        # send the form
25        return render_template('create.html')
26    else: # request.method == 'POST':
27        # read data from the form and save in variable
28        question = request.form['question']
29        answer = request.form['answer']
30
31        # store in database
32        try:
33            con = sql.connect('qa_database.db')
34            c =  con.cursor() # cursor
35            # insert data
36            c.execute("INSERT INTO tbl_QA (question, answer) VALUES (?,?)",
37                (question, answer))
38            con.commit() # apply changes
39            # go to thanks page
40            return render_template('createThanks.html', question=question)
41        except con.Error as err: # if error
42            # then display the error in 'database_error.html' page
43            return render_template('database_error.html', error=err)
44        finally:
45            con.close() # close the connection
46
47
48# Display question
49@app.route('/question/<int:id>', methods=['GET', 'POST'])
50def question(id):
51    if request.method == 'GET':
52        # send the form
53        # code to read the question from database
54        try:
55            con = sql.connect('qa_database.db')
56            c =  con.cursor() # cursor
57            # read question : SQLite index start from 1 (see index.html)
58            query = "Select question FROM tbl_QA where id = {0}".format(id)
59            c.execute(query)
60            question = c.fetchone() # fetch the data from cursor
61            con.commit() # apply changes
62            # go to thanks page : pass the value of tuple using question[0]
63            return render_template('question.html', question=question[0])
64        except con.Error as err: # if error
65            # then display the error in 'database_error.html' page
66            return render_template('database_error.html', error=err)
67        finally:
68            con.close() # close the connection
69
70        return render_template('question.html', question=question)
71    else: # request.method == 'POST':
72        # read and check answers
73        submitted_answer = request.form['answer']
74
75        # code to read the answer from database
76        try:
77            con = sql.connect('qa_database.db')
78            c =  con.cursor() # cursor
79            # read answer : SQLite index start from 1 (see index.html)
80            query = "Select answer FROM tbl_QA where id = {0}".format(id)
81            c.execute(query)
82            correct_answer = c.fetchone()[0] # fetch and store tuple-value (see [0])
83            con.commit() # apply changes
84        except con.Error as err: # if error
85            # then display the error in 'database_error.html' page
86            return render_template('database_error.html', error=err)
87        finally:
88            con.close() # close the connection
89
90        if submitted_answer == correct_answer:
91            return render_template('correct.html');
92        else:
93            return render_template('sorry.html',
94                answer = correct_answer,
95                yourAnswer = submitted_answer
96            )
97