Flask SQLite

Python has an in-assembled support for SQlite. SQlite3 module is sent with Python dissemination. For this tutorial on utilizing SQLite database in Python, if you don't mind allude to this connection. In this area we will perceive how a Flask application interacts with SQLite.

Create an SQLite database ‘database.db’ and create a students’ table in it.

import sqlite3

conn = sqlite3.connect('database.db')
print "Opened database successfully";

conn.execute('CREATE TABLE students (name TEXT, addr TEXT, city TEXT, pin TEXT)')
print "Table created successfully";

Our Flask application has three View functions.

First new_student() function is bound to the URL rule (‘/addnew’). It renders an HTML file containing student information form.

def new_student():
   return render_template('student.html')

The HTML script for ‘student.html’ is as follows −

      <form action = "{{ url_for('addrec') }}" method = "POST">
         <h3>Student Information</h3>
         <input type = "text" name = "nm" /></br>
         <textarea name = "add" ></textarea><br>
         <input type = "text" name = "city" /><br>
         <input type = "text" name = "pin" /><br>
         <input type = "submit" value = "submit" /><br>
As it can be seen, form data is posted to the ‘/addrec’ URL which binds the addrec() function.

This addrec() function retrieves the form’s data by POST method and inserts in students table. Message corresponding to success or error in insert operation is rendered to ‘result.html’.

The HTML script of result.html contains an escaping statement {{msg}} that displays the result of Insert operation.
<!doctype html>
      result of addition : {{ msg }}
      <h2><a href = "\">go back to home page</a></h2>

This list.html is a template, which iterates over the row set and renders the data in an HTML table.

<!doctype html>
      <table border = 1>
         {% for row in rows %}
               <td> {{ row["city"]}}</td>
         {% endfor %}
      <a href = "/">Go back to home page</a>

Finally, the ‘/’ URL rule renders a ‘home.html’ which acts as the entry point of the application.

def home():
   return render_template('home.html')

Here is the complete code of Flask-SQLite application.

from flask import Flask, render_template, request
import sqlite3 as sql
app = Flask(__name__)

def home():
   return render_template('home.html')

def new_student():
   return render_template('student.html')

@app.route('/addrec',methods = ['POST', 'GET'])
def addrec():
   if request.method == 'POST':
         nm = request.form['nm']
         addr = request.form['add']
         city = request.form['city']
         pin = request.form['pin']
         with sql.connect("database.db") as con:
            cur = con.cursor()
            cur.execute("INSERT INTO students (name,addr,city,pin) 
               VALUES (?,?,?,?)",(nm,addr,city,pin) )
            msg = "Record successfully added"
         msg = "error in insert operation"
         return render_template("result.html",msg = msg)

def list():
   con = sql.connect("database.db")
   con.row_factory = sql.Row
   cur = con.cursor()
   cur.execute("select * from students")
   rows = cur.fetchall();
   return render_template("list.html",rows = rows)

if __name__ == '__main__':
   app.run(debug = True)