How to Use SQLite Viewer Flask

Last Updated : 05 Jun, 2023

SQLite is a popular lightweight relational database management system that is used in a variety of applications. Flask is a popular web framework for building web applications in Python. SQLite Viewer Flask is a tool that allows you to view the contents of an SQLite database within a Flask application.

Flask is a web framework for building web applications in the Python programming language. It is a lightweight framework that provides the necessary tools and libraries to build web applications quickly and easily. In this article, we will learn how to use SQLite Viewer in Flask.

Step 1: Install Required Libraries
The first step is to install the required libraries. You will need to install Flask and SQLite3. You can install them using pip.

pip install Flask
pip install sqlite3

Step 2: Project Structure

Project Structure

Step 3: Create an SQLite Database and insert values into the database

The next step is to create an SQLite database. You can use any SQLite management tool to create an SQLite database. For this example, we will create a simple database with a single table called “users” with columns for “id”, “name”, and “email”.

sql.py

Python3

import sqlite3 
  
# connect to the database 
conn = sqlite3.connect('example.db') 
  
# create a cursor object 
c = conn.cursor() 
  
# create a table named 'users' with columns for id, name, and email 
c.execute('''CREATE TABLE users  
    (id INTEGER PRIMARY KEY, name TEXT, email TEXT) 
''') 
  
# insert five rows of data into the 'users' table 
c.execute(''' 
INSERT INTO users (name, email) VALUES  
    ('John Doe', 'johndoe@example.com'), 
    ('Jane Smith', 'janesmith@example.com'), 
    ('Bob Johnson', 'bobjohnson@example.com'), 
    ('Emily Davis', 'emilydavis@example.com'), 
    ('Mike Lee', 'mikelee@example.com'); 
''') 
  
# commit changes to the database 
conn.commit() 
  
# close the database connection 
conn.close() 

Output:

Step 4: Create Flask Application

First, we set up the project using the following commands:

touch app.py

This is a Python code for a simple Flask web application that retrieves data from a SQLite database table called ‘users’ and renders it on an HTML template called ‘index.html’. The Flask app is created using the Flask class, and a route is defined for the default URL. When the route is accessed, the app connects to the database, retrieves all the data from the ‘users’ table, pass it as a variable to the ‘index.html’ template, and renders the template with the data. Finally, the app is run in debug mode using the run() method.

app.py

Python3

# import Flask and render_template 
from flask import Flask, render_template 
# import sqlite3 library for database connection 
import sqlite3 
  
# create a Flask instance called app 
app = Flask(__name__) 
  
# route the default URL 
  
  
@app.route('/') 
def home(): 
    # connect to the example.db database 
    conn = sqlite3.connect('example.db') 
    # create a cursor object to execute SQL commands 
    c = conn.cursor() 
    # execute a SQL command 
    c.execute('SELECT * FROM users') 
    # retrieve all the data from the users table 
    users = c.fetchall() 
    # close the database connection 
    conn.close() 
    # render the index.html template 
    return render_template('index.html', users=users) 
  
  
if __name__ == '__main__': 
    app.run(debug=True) 

Step 5: Create an HTML Template
Finally, we need to create an HTML template that will display the contents of our SQLite database.

mkdir templates
cd templates
touch index.html

index.html

HTML

<!DOCTYPE html> 
<html> 
<head> 
   <title>SQLite Viewer Flask</title> 
</head> 
<body> 
   <h1>Users</h1> 
   <table> 
       <tr> 
           <th>ID</th> 
           <th>Name</th> 
           <th>Email</th> 
       </tr> 
       {% for user in users %} 
           <tr> 
               <td>{{ user[0] }}</td> 
               <td>{{ user[1] }}</td> 
               <td>{{ user[2] }}</td> 
           </tr> 
       {% endfor %} 
   </table> 
</body> 
</html>