Reading/Fetching Data from Database in Python using SQLite3

This tutorial mainly focuses on:

• Reading data from Database in SQLite in Python
• Reading One record from database in python using SQLite
• Reading Multiple record from database in python using SQLite
• Reading All records from database in python using SQLite

In previous tutorial, we have already learnt about how to insert data in our database in python using SQLite? In this tutorial, we will learn to fetch or read data from database in SQLite in Python.

Our database, and table present inside the database looks like:

database name: “data.db”
table name: “users”
table columns: “id“, “name“, “age“, “gender”

One Record – Python program for reading/fetching data from database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data fetching Process-(One Record)
    query = "SELECT * from USERS"
    x = cursor.execute(query).fetchone()
    print(x)
    #format
    print(f" ID is {x[0]} Age is {x[1]} & Name is {x[2]}")
except:
    print("Database Error")

#END

Output:

# Connected to Database Successfully
# (1, 'Raj', 21, 'M')
# ID is 1 Age is Raj & Name is 21

«Hence we have successfully fetched data from our database table»

Explanation: I will only explain the main points or the points which are totally new for us. Because as we are working with databases, so I’m assuming that you guys are aware of core concepts of python.

• At line 4 we have imported our sqlite3 library, because the functions we used in our program is already pre-defined in sqlite3 library.
• At line 7 & 17 we used try: & except: method which is useful for handling sql exception.
• At line 8 & 9 we initialized “con” variable with connect() method with our database name “data.db“. The connect() method used here is pre-defined in sqlite3 library.
• At line 12 we initialized variable “query” with sql query for fetching data. Here “users” is the name of our table present inside our database “data.db”.
• At line 13 cursor.execute(query).fetchone() here cursor storing the database connection & execute() is the function defined in sqlite3 library. The function execute() takes one parameter “sql-query” and also fetchone() function is used to fetch only one data from database.
• At line 14 we are printing the data we read from database. It returns us the output in the form of tuple.
• At line 16 we displayed the data in user readable form.

Multiple Records – Python program for reading/fetching data from database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data fetching Process-(Many Record)
    query = "SELECT * from USERS"
    x = cursor.execute(query).fetchmany(2)
    for i in x:
        print(f" ID is {i[0]} Age is {i[1]} & Name is {i[2]}\n")
except:
    print("Database Error")

#END

Output:

# Connected to Database Successfully
# ID is 1 Age is Raj & Name is 21

# ID is 2 Age is Rohit & Name is 23

Explanation: Program explanation is same as, explained above the only difference in the program is ” At line 13 in place of fetchone() function we used fetchmany() many function. This function takes one parameter as value. The “value” is the size of the data we want to fetch. If we have 10 entries in our database then we can fetch maximum of 10 entries or we can pass max. of 10 as parameter.

All Records – Python program for reading/fetching data from database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data fetching Process-(ALL Records)
    query = "SELECT * from USERS"
    x = cursor.execute(query).fetchall()
    for i in x:
        print(f" ID is {i[0]} Age is {i[1]} & Name is {i[2]}\n")
except:
    print("Database Error")

#END

Output:

# Connected to Database Successfully
# ID is 1 Age is Raj & Name is 21

# ID is 2 Age is Rohit & Name is 23

Explanation: Program explanation is same as, explained above the only difference in the program is ” At line 13 in place of fetchone()/fetchmany() function we used fetchall() function. This function returns all the available records from the database.