How to use Flask sqlite3 Package [Step by Step Guide]

by

How to use Flask sqlite3 Package [Step by Step Guide]

In this tutorial, we will learn about How to use Flask sqlite3 package by following step by step guide. The Flask’s sqlite3 package is an interface which helps the user to connect to the sqlite3 database using Python code instead of manually interacting with the database. This is one of the powerful tool for the developers who has to interact with the databases in day to day life work for data insertion, modification cleaning and so on. We will look at few examples to understand how the sqlite3 package is used and what all features it offers to its users. So, let us begin the tutorial.

 

Sqlite3 Package Overview

In Python, sqlite3 module provides an interface for interacting with SQLite databases. It is a light weighted, serverless, self-contained and file-based relational database engine. We can use sqlite3 package to perform all SQL operation via Python program. For example, table creation, data insertion, data filtering, data fetching and so on.  In the upcoming section we will look at some basic SQL operations which is done using Python program.

 

How to use Flask sqlite3 Package [Step by Step Guide]

Also Read: How to Install PostgreSQL on Windows [Step by Step Guide]

As mentioned earlier, sqlpite3 module enables the user to connect to sqlite3 database and allows to perform all SQL operations. We will use Python code examples to demonstrate the database operations like connect to sqlite3 database, create table, insert data and fetch data. Let us look at each use case one by one in the next section.

Prerequisite

  • Windows Operating System Installed
  • VS Code or any other IDE installed
  • Flask Installed

 

1.  Add and Fetch One Record 

In this example, we will first create a database file and then perform all the SQL operations in this database file. Save the below code in a file with extension .py. Before executing the code, look at the code explanation which is given after the code to understand the usage of each statement in the code.

Example-1

import sqlite3

connection = sqlite3.connect('events.db')
cursor = connection.cursor()

cursor.execute('''CREATE TABLE IF NOT EXISTS Events(EventID INT, EventName TEXT, EventOwner TEXT )''')
cursor.execute('''INSERT INTO Events VALUES('1432', 'SQLite Webinar', 'LinuxNasa') ''')
cursor.execute('''SELECT * FROM Events''')

print(cursor.fetchone())

connection.commit()
connection.close()
OUTPUT
> python3 .\app.py
(1432, 'SQLite Webinar', 'LinuxNasa')

 

Code Explanation
To use the sqlite3 package, import the package using “import sqlite3”.
Connect to sqlite3 database using  function sqlite3.connect(‘<database-file>’).
Initialize a cursor using function connection.cursor().
Create new table in the database using function cursor.execute(”'<SQL Query to Create Database>”’).
Insert data in the new table using function cursor.execute(”'<SQL Query to Insert Data in Table>”’).
Fetch data from the table using function cursor.execute(”'<SQL Query to Fetch Data From Table>”’).
Print fetched data from the table on the console using function cursor.fetchone(). This function will print only first record from the table.
Commit the changes in the database using function connection.commit().
Close the database active session using function connection.close().

 

2.  Add many and Fetch all Records

In the previous example, we connected to the database, created a table and inserted one record in the table. In real time use cases, we insert more than one record in the table. Sqlite3 provides a function called executemany() which allows the user to insert more than one record at a time in the table.

We also saw in the previous example how to print the first record after fetching from the table. To fetch all the existing records in the table, sqlite3 provides a function called fetchall() which fetches and prints all the records from the table. I have demonstrated both the sqlite3 function implementation in the below example.

Example-2

import sqlite3

connection = sqlite3.connect('events.db')
cursor = connection.cursor()
upcomingEvents = [('1433', 'World of AI/ML', 'Robert Michael'), ('1434', 'Advanced Python', 'Jammie Albert '), ('1435', 'Advanced SQLite Database', 'LinuxNasa')]

cursor.executemany('INSERT INTO Events VALUES(?,?,?)', upcomingEvents)
cursor.execute('SELECT * FROM Events')

print(cursor.fetchall())

connection.commit()
connection.close()
OUTPUT
> python3 .\app.py
[(1432, 'SQLite Webinar', 'LinuxNasa'),  (1433, 'World of AI/ML', 'Robert Michael'), (1434, 'Advanced Python', 'Jammie Albert '), (1435, 'Advanced SQLite Database', 'LinuxNasa')]

 

3.  Filter Records in SQLite DB

This example is similar to the previous two examples with the only difference that here we will add some logic to filter out the data based on conditions. To achieve this, we use sqlpite3 function called execute(). We passes the SQL query as an argument to this function which in turn will return only those records from the table which met the SQL query conditions. I have demonstrated this use case in the below example.

Example-3

import sqlite3

connection = sqlite3.connect('events.db')
cursor = connection.cursor()
eventID = (1431,)

cursor.execute('SELECT * FROM Events WHERE EventID=?', eventID)

print(cursor.fetchall())

connection.commit()
connection.close()
OUTPUT
//For eventID 1434
> python3 .\app.py 
[(1434, 'Pandas Webinar', 'LinuxNasa')]

//For eventID 1431
> python3 .\app.py 
[]

 

Summary

We have learnt about Python sqlite3 module to connect to sqlpute3 databases and perform all SQL queries using different examples. This module has much features that what we covered in this tutorial. To explore the module thoroughly, I suggest you to checkout sqlite3 module official page.

Leave a Comment