flask form to database code example

Example: how to save form data to database in flask\

# 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
            )