How to read data from Table of SQLite3 Database using Python

Read Data from Table of SQLite3 Database in Python

Read Data from SQLite3 Database Table

Reading a data is an important part of any Database operation and can be read in 3 ways

• Single row data
• All data in table
• A specific number of data

Below is the data what we have in our database with table_name as Employee_Data and Id, Name and Salary as column name

Below is a program which will execute fetch API to read  the data from database

 # program

 import sqlite3 as sql

 conn = sql.connect("Employee_Details.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.execute("select * from Employee_Data")
 result = fetch API to call here
 print("result : ", result)
 conn.close()

• to fetch single row

Actual query to be replaced in above program

 result = curs.fetchone()

 #Output

 result :  (101, 'Er M S Dandyan', 28602.08)

• to fetch all row

Actual query to be replaced in above program

 result = curs.fetchall()

 #Output

 result :  [(101, 'Er M S Dandyan', 28602.08), (102, 'Techno Xpresss', 29602.08), (103, 'Mandeep Singh', 27602.08)]

• to fetch many row

Actual query to be replaced in above program

 result = curs.fetchmany(3)

This will read 3 data from the Table

 #Output

 result :  [(101, 'Er M S Dandyan', 28602.08), (102, 'Techno Xpresss', 29602.08), (103, 'Mandeep Singh', 27602.08), (104, 'Mandy', 24602.08), (105, 'Mandeep', 20602.08)]

The return of 

• fetch one is a tuple
• fetchall()  is a List of Tuples
• fetchmany(size) is a List of Tuples

Example :

 # program

 import sqlite3 as sql

 conn = sql.connect("Employee_Details.db")     
 curs = conn.cursor()                    

 curs.execute("select * from Employee_Data")
 fetch_one = curs.fetchone()
 print("fetch_one     : ", fetch_one)

 curs.execute("select * from Employee_Data")
 fetch_many = curs.fetchmany(3)
 print("fetch_many(3) : ", fetch_many)

 curs.execute("select * from Employee_Data")
 fetch_all = curs.fetchall()
 print("fetch_all     : ", fetch_all)

 conn.close()

 #Output

 fetch_one     :  (101, 'Er M S Dandyan', 28602.08)
 fetch_many(3) :  [(101, 'Er M S Dandyan', 28602.08), (102, 'Techno Xpresss', 29602.08), (103, 'Mandeep Singh', 27602.08)]
 fetch_all     :  [(101, 'Er M S Dandyan', 28602.08), (102, 'Techno Xpresss', 29602.08), (103, 'Mandeep Singh', 27602.08), (104, 'Mandy', 24602.08), (105, 'Mandeep', 20602.08)]

to know more data reading in SQLite3 Python, please read select query as the data what to read is totally based on Select queries

Example to read an empty table :

database:

 # program

 import sqlite3 as sql

 conn = sql.connect("mydatabase.db")
 curs = conn.cursor()

 curs.execute("select * from Employee_Data")
 fetch_one = curs.fetchone()
 print("fetch_one     : ", fetch_one)

 curs.execute("select * from Employee_Data")
 fetch_many = curs.fetchmany(3)
 print("fetch_many(3) : ", fetch_many)

 curs.execute("select * from Employee_Data")
 fetch_all = curs.fetchall()
 print("fetch_all     : ", fetch_all)

 conn.close()

 #Output

 fetch_one     :  None
 fetch_many(3) :  []
 fetch_all     :  []

 

so output shows the return of

• fetch one is NONE if data is not available
• fetchall()  is empty LIST if data is not available
• fetchmany(size) is empty LIST if data is not available