Flask SQLite

Flask is a popular Python web framework that allows developers to easily build web applications with minimal setup and boilerplate code. One of the many features of Flask is its support for integrating databases, including SQLite, a lightweight, serverless, and self-contained relational database management system. In this article, we will explore how to use Flask with SQLite to build powerful web applications.

Why SQLite with Flask?

SQLite is a simple and self-contained database engine that does not require a separate server process, making it an ideal choice for small to medium-sized applications. Flask, on the other hand, is a micro-framework that provides just the essentials for building web applications. It allows developers to have full control over the architecture and design of their applications.

When combined, Flask and SQLite provide a powerful and flexible solution for building web applications that require a database without the overhead of setting up and managing a separate database server.

Prerequisites for using SQLite with Flask

SQLite is a popular lightweight, serverless, self-contained, and file-based relational database management system (RDBMS) that is often used with Flask web applications. Here are the prerequisites for using SQLite with Flask:

1. Python and Flask:

You need to have Python installed on your system, as well as the Flask library. Flask is a Python web framework that allows you to easily build web applications, including those that use SQLite as the database.

2. SQLite Library:

SQLite is included as a built-in module in Python, so you don’t need to install any additional libraries to use SQLite with Flask.

3. SQLite Database:

You will need a SQLite database file to store your data. SQLite databases are simply files with a .db or .sqlite extension that store data in a structured format.

4. SQLalchemy (optional):

SQLalchemy is a popular Object Relational Mapper (ORM) library that allows you to interact with databases using Python classes instead of raw SQL queries. While it’s not a prerequisite for using SQLite with Flask, it can greatly simplify database operations and provide additional features.

5. Basic SQL knowledge:

While Flask and SQLite abstract much of the complexity of working with databases, having a basic understanding of SQL (Structured Query Language) can be beneficial. It helps you understand how to create tables, define relationships, and perform CRUD (Create, Read, Update, Delete) operations on your SQLite database.

These are the main prerequisites for using SQLite with Flask. Once you have Python, Flask, an SQLite database file, and optionally SQLalchemy, you can start building Flask web applications that leverage the power of SQLite for data storage and retrieval.

Setting up Flask with SQLite

To get started with Flask and SQLite, you will need to have Python and Flask installed on your machine. Once you have them installed, you can create a new Flask project by following these steps:

Step 1: Create a Virtual Environment

It is a best practice to create a virtual environment for your Flask project to isolate dependencies. You can create a virtual environment using the following command in your terminal or command prompt:

python -m venv myenv

This will create a new virtual environment named “myenv” in your current directory.

Step 2: Activate the Virtual Environment

Next, you need to activate the virtual environment. On Windows, you can do this with the following command:

myenv\Scripts\activate

On macOS and Linux, you can use the following command instead:

source myenv/bin/activate

Step 3: Install Flask

With the virtual environment activated, you can now install Flask using the following command:

pip install flask

Step 4: Create a Flask Application

Once Flask is installed, you can create a new Flask application by creating a Python script with a “.py” extension. Here’s an illustration of a basic Flask application:

from flask import Flask

app = Flask(__name__)

@app.route('/')
def pythongeeks_hello():
   return 'Hello, Flask with SQLite!'

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

In this example, we imported Flask, created a Flask web application instance. We defined a route for the root URL (“/”) that returns a “Hello, Flask with SQLite!” message, and started the application with app.run().

Step 5: Initialize SQLite Database

Now that our Flask application is set up, we can add SQLite support to it. Flask provides a built-in module called “sqlite3” for working with SQLite databases. To use it, we need to import it in our Flask application and initialize a SQLite database connection.

Here’s an example of how to initialize a SQLite database connection in Flask:

import sqlite3
from flask import Flask


app = Flask(__name__)


@app.route('/')
def pythongeeeks_hello():
   conn = sqlite3.connect('mydb.sqlite')  # Connect to SQLite database
   conn.close()  # Close database connection
   return 'Hello, Flask with SQLite!'


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

In this example, we used the sqlite3.connect() function to connect to a SQLite database named “mydb.sqlite”. You can replace “mydb.sqlite” with the name of your SQLite database file. We also closed the database connection using the conn.close() method to release resources after use.

Working with SQLite Database in Flask

Once the SQLite database connection is established, we can perform various operations such as creating tables, inserting data, querying data, updating data, and deleting data. Let’s take a look at some examples of how to work with SQLite database in Flask.

1. Creating Tables:

To create a table in SQLite using Flask, you can use the following example:

import sqlite3
from flask import Flask


app = Flask(__name__)


@app.route('/')
def pythongeeks_hello():
   conn = sqlite3.connect('mydb.sqlite')
   cursor = conn.cursor()


   # Create a table
   cursor.execute('''CREATE TABLE IF NOT EXISTS users
                     (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     name TEXT NOT NULL,
                     age INTEGER NOT NULL)''')
                    
   conn.commit()
   conn.close()
   return 'Table created successfully!'


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

Output – 

In this example, we used the cursor.execute() method to execute a SQL query that creates a table named “users” with three columns: “id”, “name”, and “age”. The conn.commit() method is called to commit the changes to the database, and the connection is closed with conn.close().

2. Inserting Data:

To insert data into a SQLite table using Flask, you can use the following example:

import sqlite3
from flask import Flask


app = Flask(__name__)


@app.route('/')
def pythongeeks_hello():
   conn = sqlite3.connect('mydb.sqlite')
   cursor = conn.cursor()


   # Insert data into the table
   cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('John', 25))
  
   conn.commit()
   conn.close()
   return 'Data inserted successfully!'


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

Output –

In this example, we used the cursor.execute() method with a SQL query that inserts data into the “users” table. We used placeholders (“?”) in the query to prevent SQL injection attacks, and passed the actual values to be inserted as a tuple in the second argument of the cursor.execute() method.

3. Querying Data:

To query data from a SQLite table using Flask, you can use the following example:

import sqlite3
from flask import Flask


app = Flask(__name__)


@app.route('/')
def pythogeeks_hello():
   conn = sqlite3.connect('mydb.sqlite')
   cursor = conn.cursor()


   # Query data from the table
   cursor.execute("SELECT * FROM users")
   result = cursor.fetchall()
  
   conn.close()
   return str(result)


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

In this example, we used the cursor.execute() method with a SQL query that selects all the rows from the “users” table. We then used the cursor.fetchall() method to fetch all the rows returned by the query. The fetched data is returned as a list of tuples, which can be further processed as needed.

4. Updating Data:

To update data in a SQLite table using Flask, you can use the following example:

import sqlite3
from flask import Flask


app = Flask(__name__)


@app.route('/')
def pythongeeks_hello():
   conn = sqlite3.connect('mydb.sqlite')
   cursor = conn.cursor()


   # Update data in the table
   cursor.execute("UPDATE users SET age=? WHERE name=?", (30, 'John'))
  
   conn.commit()
   conn.close()
   return 'Data updated successfully!'


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

Output

In this example, we used the cursor.execute() method with a SQL query that updates the “age” column of the “users” table for a row with the name ‘John’. To pass the values to be updated as a tuple in the cursor’s second argument, we utilised placeholders (“?”) in the query.implement() method. The conn.commit() method is called to commit the changes to the database, and the connection is closed with conn.close().

5. Deleting Data:

To delete data from a SQLite table using Flask, you can use the following example:

import sqlite3
from flask import Flask

app = Flask(__name__)

@app.route('/')
def pythongeek_hello():
   conn = sqlite3.connect('mydb.sqlite')
   cursor = conn.cursor()


   # Delete data from the table
   cursor.execute("DELETE FROM users WHERE name=?", ('John',))
  
   conn.commit()
   conn.close()
   return 'Data deleted successfully!'


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

Output

In this example, we used the cursor.execute() method with a SQL query that deletes a row from the “users” table where the name is ‘John’. We used a placeholder (“?”) in the query to pass the value to be deleted as a tuple in the second argument of the cursor.execute() method. The conn.commit() method is called to commit the changes to the database, and the connection is closed with conn.close().

Conclusion

Flask provides a simple and easy-to-use way to work with SQLite databases. However, it’s important to keep in mind that SQLite is a serverless, self-contained database engine that is best suited for small-scale applications or for local development and testing. If you need to build a production-scale application with heavy concurrent access or high write loads, you may need to consider other more robust database options.

In conclusion, Flask with SQLite is a powerful combination that allows you to easily incorporate a lightweight, serverless, and self-contained database engine into your Flask applications. You can create tables, insert data, query data, update data, and delete data using Flask’s built-in SQLite support. With Flask’s simplicity and flexibility, you can quickly build small to medium-sized web applications that require data persistence.