Flask with SQLite

Developed in Python, Flask is a microweb platform. It is classified as a micro-framework because it does not require particular tools or libraries. The Werkzeug WSGI library and Jinja2 template engine serve as the foundation for Flask. Flask supports various database management systems such as MySQL, PostgreSQL, SQLite, etc. SQLite is a serverless database management system and it is also the most used database system for web applications.

Prerequisites to use Flask with SQLite

• Basic knowledge of Python
• Knowledge of fundamental Flask ideas like routes, view functions, and templates.
• Working knowledge of HTML fundamentals.
• Fundamental knowledge of MySQL usage.

Installation of Flask and SQLite

To use Flask with SQLite, we need to install Flask and SQLite. To install Flask, we can use pip, which is a package manager for Python. Open python terminal and run the command:

pip install Flask

To install SQLite, we can download the SQLite shell or we can use a library called sqlite3, which comes with Python. We need not install it separately.

Creating a Flask application

To create a Flask application, we need to import Flask and create an instance of the Flask class. We also need to define a route, which is a URL path that the application will respond to. Create a file called app.py and add the following code:

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello():
    return 'Hello, World!'

In the above code, we import Flask and create an instance of the Flask class. The code defines a route for the root URL path (‘/’) in Flask, and when a request is made to this path, the hello function is called, which returns the string ‘Hello, World!’.

To run the application, open a terminal and run the following command:

export FLASK_APP=app.py
flask run

This will start the development server and the application will be available at http://127.0.0.1:5000.

Creating a database with SQLite

To create a database with SQLite, we can use the sqlite3 library, which comes with Python. Create a file db.py and add the code:

import sqlite3

conn = sqlite3.connect('example.db')

In the above code, we import the sqlite3 library and create a connection to a database called example.db. The database will be made if it doesn’t already exist. If it does, a link will be made to the current database.

Creating and inserting data into the table

To create a table and insert data into the table, we need to execute SQL statements. SQLite supports a subset of SQL, which is sufficient for most web applications. Create a file called models.py and add the following code:

import sqlite3

def create_table():
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  name TEXT NOT NULL,
                  email TEXT NOT NULL,
                  age INTEGER)''')
    conn.commit()
    conn.close()

def insert_user(name, email, age):
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", (name, email, age))
    conn.commit()
    conn.close()

In the above code, we defined two functions: create_table and insert_user. The create_table function creates a table called users if it does not already exist. The table has four columns: id, name, email, and age. The id column is an auto-incrementing primary key, which means that a unique id will be generated for each row. The name and email columns are required and cannot be null. The age column is optional and can be null.

The insert_user function inserts a new user into the users table. The function takes three parameters: name, email, and age. The function executes an SQL statement to insert a new row into the table with the values of the parameters. The SQL statement uses placeholders (?, ?, ?) to prevent SQL injection attacks.

To use these functions, we need to call them from our Flask application. Modify the app.py file as follows:

from flask import Flask, render_template
from models import create_table, insert_user

app = Flask(__name__)

create_table()

@app.route('/')
def home():
    insert_user('John Doe', '[email protected]', 30)
    return 'User inserted'

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

In the above code, we import the create_table and insert_user functions from the models.py file. We call the create_table function when the application starts to ensure that the users table exists. The code defines a route for the root URL path (‘/’) in a web application. When a request is made to this path, the home function is called. The home function inserts a new user into the users table. The function will return the string ‘User inserted’.

Retrieving data from the table

To retrieve data from the users table, we need to execute an SQL query. Create a new function called get_users in the models.py file:

import sqlite3

def get_users():
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute("SELECT * FROM users")
    rows = c.fetchall()
    conn.close()
    return rows

The get_users function executes an SQL query to retrieve all rows from the users table. The fetchall method returns a list of tuples, where each tuple represents a row in the table.

Modify the app.py file to display the users in the browser:

from flask import Flask, render_template
from models import create_table, insert_user, get_users

app = Flask(__name__)

create_table()

@app.route('/')
def home():
    insert_user('John Doe', '[email protected]', 30)
    users = get_users()
    return render_template('home.html', users=users)

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

In the above code, we import the get_users function from the models.py file. We call the function to retrieve all users from the users table. We pass the users to the home.html template using the render_template function.

Create a new file called home.html in a templates folder and add the following code:

<!DOCTYPE html>
<html>
<head>
    <title>Users</title>
</head>
<body>
    <h1>Users</h1>
    <ul>
        {% for user in users %}
        <li>{{ user }}</li>
        {% endfor %}
    </ul>
</body>
</html>

In the above code, we use the Jinja2 template engine to loop through the users list and display each user in an unordered list.

Updating data in the table

To update data in the users table, we need to execute an SQL statement. Create a new function called update_user in the models.py file:

import

def update_user(user_id, name, email, age):
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute("UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?", (name, email, age, user_id))
    conn.commit()
    conn.close()

The update_user function updates a user in the users table with the specified id. The function takes four parameters: user_id, name, email, and age. The function executes an SQL statement to update the row with the specified id. The SQL statement uses placeholders (?, ?, ?, ?) to prevent SQL injection attacks.

Modify the app.py file to update a user in the browser:

from flask import Flask, render_template, request
from models import create_table, insert_user, get_users, update_user

app = Flask(__name__)

create_table()

@app.route('/')
def home():
    insert_user('John Doe', '[email protected]', 30)
    users = get_users()
    return render_template('home.html', users=users)

@app.route('/update', methods=['POST'])
def update():
    user_id = request.form['user_id']
    name = request.form['name']
    email = request.form['email']
    age = request.form['age']
    update_user(user_id, name, email, age)
    return 'User updated'

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

In the above code, we define a new route called ‘/update’ that accepts a POST request. When the form on the home.html template is submitted, the data is sent to this route. We extract the values of the form fields from the request object and pass them to the update_user function. The function updates the user in the users table with the specified id. The function returns the string ‘User updated’.

Modify the home.html template to include a form to update a user:

<!DOCTYPE html>
<html>
<head>
    <title>Users</title>
</head>
<body>
    <h1>Users</h1>
    <ul>
        {% for user in users %}
        <li>{{ user[1] }} - {{ user[2] }} - {{ user[3] }}
            <form action="/update" method="POST">
                <input type="hidden" name="user_id" value="{{ user[0] }}">
                <input type="text" name="name" value="{{ user[1] }}">
                <input type="text" name="email" value="{{ user[2] }}">
                <input type="text" name="age" value="{{ user[3] }}">
                <input type="submit" value="Update">
            </form>
        </li>
        {% endfor %}
    </ul>
</body>
</html>

In the above code, we add a form to each user in the unordered list. The form contains hidden input field with the user id. The form also contains input fields for the name, email, and age of the user. When the form is submitted, the data is sent to the ‘/update’ route using the POST method.

Deleting data from the table

To delete data from the users table, we need to execute an SQL statement. Create a new function called delete_user in the models.py file:

def delete_user(user_id):
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute("DELETE FROM users WHERE id = ?", (user_id,))
    conn.commit()
    conn.close()

Conclusion

The article by DataFlair teaches how to build a simple web application using Flask and SQLite. The application is capable of performing basic CRUD operations on a database table. The article covers how to create a table in SQLite, insert data into it, retrieve data from it, update data in it, and delete data from it. We also looked at how to display the data in the table on a web page using Flask’s templating system.