Do you think IIT Guwahati certified course can help you in your career?
Introduction
Flask is an open-source micro web application framework written in python. Flask provided us with a simple interface with fewer lines of code. This helps developers to build web applications easily. We know how to integrate flask into our UI through modules and extensions like WTforms, Mails, etc. It also supports routing, rendering, Debugging, configuration handling, etc. But does flask support the backend too?
And the answer is yes. Flask provides built-in support for a module, SQLite. Let’s know more about SQLite and how flask supports it further in this article.
SQLite
SQLite is an open-source relational database system written in the C programming language. It implements a fast, reliable, and full-featured SQL database engine. It uses SQL query language to interact with the database. The SQLite database stores all the data in a single file to make it easy for the developer to perform queries and fetch data. Let’s see an example of SQLite3 with flask.
import sqlite3
from flask import g
DATABASE = '/path/to/database.db'
def get_db():
db = getattr(g, '_database', None)
if db is None:
db = g._database = sqlite3.connect(DATABASE)
return db
@app.teardown_appcontext
def close_connection(exception):
db = getattr(g, '_database', None)
if db is not None:
db.close()
You can also try this code with Online Python Compiler
An application must have an active application context or create on its own to use the database. In the above code, the get_db() method is used to get/ establish a database connection and destroyed or released using close_connection() if we no longer need it. Now let’s see how to create a CRUD application in flask.
CRUD application
A CRUD is a Create - Read - Update - Delete application in the flask. Let’s explore how to create a database.
Create database
We have to create a table using the create query in SQL to create a database. The query used to create a table is as follows.
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
);
The above query creates a table with all the variables. You can learn more about SQL commands from this article. Let’s create a database using this command.
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";
conn.close()
You can also try this code with Online Python Compiler
In the above code, we imported sqlite3 into our flask application and established the connection to a database. After the table is created successfully, it prints the message "Table created successfully" to let the user know that a table is created. Then it releases or closes the connection.
Read database
We have a view function in the flask: index(). It is associated with the URL and renders the html file.
The form data will be published to the ‘/addrec’ URL of the binding addrec () function. It retrieves the form data through POST or GET methods and inserts data into students table
@app.route('/addrec',methods = ['POST', 'GET'])
def addrec():
if request.method == 'POST':
try:
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))
con.commit()
msg = "Record successfully added"
except:
con.rollback()
msg = "error in insert operation"
finally:
return render_template("result.html",msg = msg)
con.close()
You can also try this code with Online Python Compiler
The result of the insertion in this file needs to be shown to the user. The corresponding message will be inside a file result.html.
<!doctype html>
<html>
<body>
Result : {{ msg }}
<h2><a href = "\">Back to Home page</a></h2>
</body>
</html>
The above code shows your result and a link that redirects back to the home page on click.
Delete database
<!DOCTYPE html>
<html>
<head>
<title>delete record</title>
</head>
<body>
<h3>Remove student from the list</h3>
<form action="/deleterecord" method="post">
Student name <input type="text" name="name">
<input type="submit" value="Submit">
</form>
</body>
</html>
@app.route("/deleterecord",methods = ["POST"])
def deleterecord():
name = request.form["name"]
with sqlite3.connect("database.db") as con:
try:
cur = con.cursor()
cur.execute("delete from students where name = ?",name)
msg = "record successfully deleted"
except:
msg = "can't be deleted"
finally:
return render_template("delete_record.html",msg = msg)
You can also try this code with Online Python Compiler
The above code deletes the field or a student from the table based on the given name.
Frequently Asked Questions
What is SQLite in flask?
SQLite is an open-source relational database management system written in the C programming language. It implements a fast, reliable, and full-featured SQL database engine. It uses SQL query language to interact with the database.
What is a CRUD application?
A CRUD is a Create - Read - Update - Delete application done in flask by using SQLite. It can create, display, edit/update, and delete the records in a database or table.
What is the difference between MySQL and SQLite?
MySQL operates a database server through a network, which the client can then access. However, SQLite is an example of an embedded database.
Conclusion
We have discussed SQLite, connecting SQLite to the database, CRUD application in this article.
If you like to learn more about Flask and create applications using it, go through the course we suggest and create connect SQLite to your project.
27+ registered 
