How to read data from a database using Sqlite 3 in flask

4 min readDec 29, 2021

So in my previous story , we learnt how to establish sqlite database connection in our flask app and today we will learn how to read the data from the database.

For new readers, who are new to this blog , I will explain the steps again .

so let’s get started

Step 1 : create a folder with name ‘TestingDatabase’ . You can name anything you want .

Step 2 : Open this folder in VScode or any other test editor you want to open .

Open the folder testingdatabase in vscode

Step 3 : Install Virtual Environment.( If installed already well and good , otherwise open Terminal and run the following commands).

command 1

pip install virtualenv

comamnd 2

python3 -m venv env

If running on windows then you can write this command

py3 -m venv env

Step 4 : Now its time to create some files and folders (Make sure you are creating these in the main folder testingdatabase not in folder env).

so, first create a python file with name ‘main.py’ and a folder with name templates and in templates folder create a html file with name ‘index.html’.The folder structure will look like the following.

Step 5 : Now before we proceed further , let’s activate our virtual environment and install flask.

For that run the following command

command 1

cd env

comamnd 2

source bin/activate

command 3

pip install flask

comamnd 4 (just to come back in our main folder Testing Database)

cd ..

Step 6 : Next copy paste the following code in both files i.e. main.py and index.html

Main.py

import sqlite3from flask import Flask, render_templateapp = Flask(__name__)
@app.route('/')def index():    con = sqlite3.connect("testAsh.db")    con.row_factory = sqlite3.Row    cur = con.cursor()    cur.execute("select * from ProfileData")    rows = cur.fetchall();    return render_template('index.html',posts = rows)if __name__ == '__main__':    app.run(debug=True)

Index.html

<!DOCTYPE html><html lang="en"><head><title>Testing DataBase</title><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1"><link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"><script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script><style>body {background-color: #bcf0cc;}</style></head><body ><table>{%for post in posts %}     <tr>        <td>{{ post['FirstName'] }}</td>        <td>{{ post['LastName'] }}</td>        <td>{{ post['gmail'] }}</td>    </tr> {%endfor%}</table></body></html>

Dont run the code for now as you might be getting error . The reason is , we had not created any database or Table.

Step 7 : So , next is ,we need to create a database ‘testAsh.db’ in our testingdatabase folder.

For that open that folder in terminal and run the following command

sqlite3 testAsh.db

Step 8 : It’s time to create a table name ‘ProfileData’ with 3 fields

There are many ways to do that but I recommend downloding db browser for sqlite and Install that.

You can manually create table or execute the follwing sql command

CREATE TABLE "ProfileData" (
 "id" INTEGER NOT NULL,
 "FirstName" TEXT,
 "LastName" TEXT,
 "gmail" TEXT,
 PRIMARY KEY("id" AUTOINCREMENT)
);

click on browse data option and enter the fields

Step 9 : Let’s try to run our code

python main.py

and you should be able to see this output in http://127.0.0.1:5000

So, I hope you enjoy reading this article . In next article we will learn how to add data to a data from flask.

Until then stay tunned and Happy Coding!!!

good bye