How to read/search data from database of SQLite3 database engine in python

hi, how are you today




Till now we discussed
How  to create database in sqlite3 database engine in Python
How  to create table in database in sqlite3 database engine in Python.
How  to insert data from database in sqlite3 database engine in Python

So today, taking one more step further, we will discuss how to read or search data in table inside database of SQLite3 database engine.

We are continuing with the same example of student database that we have discussed in previous post of creating table and inserting data.

Reading Data from database

There are three ways to read/search data from database

• To read all data (fetchall())
• To read specific data(fetchone())
• To read specific no of data(fetchmany())

[*Note: fetchmany() will be used when u need to read more than one data but not all data]

As explained earlier, curs is optional not mandatory while creating table and inserting data but mandatory for reading, updating and deleting data.

Fetchall()

Syntax/Query to read all data from database

 curs.execute("select * from table_name")   
 result = curs.fetchall()  

combine code for how to read all data from database

01:  #WAP to understand the concept of reading all data from DataBase  
02:  i=0  
03:  import sqlite3 as sql  
04:  conn=sql.connect("mydatabase.db")  
05:  curs=conn.cursor()  
06:  curs.execute("select * from St_data")  
07:  result = curs.fetchall()  
08:  for x in result:  
09:    i=i+1  
10:    print(i,x)  
11:  conn.close()  

Read data is in form of "list of tuple(s)", i.e. tuples as an element of list

Fetchone()

Syntax/Query to read specific data from database

 curs.execute("select * from table_name where column_data=?",(data_to_search,))  
 result = curs.fetchone()  

combine code for how to read one value from database

01:  #WAP to understand the concept of reading data from DataBase single entry  
02:  s_Id=int(input("enter product name to search: "))
03:  import sqlite3 as sql  
04:  conn=sql.connect("mydatabase.db")  
05:  curs=conn.cursor()  
06:  curs.execute("select * from St_data where Student_Id=?",(s_Id,))  
07:  result = curs.fetchone()  
08:  if result==None:  
09:    print("Product not found")  
10:  else:  
11:    print(result)  
12:  conn.close()  

Read data is in form of "list of tuple(s)", i.e. tuples as an element of list

Fetchmany()

Syntax/Query to read not all but more than one data from database

 curs.execute("select * from table_name ")  
 result = curs.fetchmany(count)  

Count means from starting how many values you want to search

combine code for how to read many values from database

1:  #WAP to understand the concept of reading data from DataBase  
2:  i=0  
3:  import sqlite3 as sql  
4:  conn=sql.connect("mydatabase.db")  
5:  curs=conn.cursor()  
6:  curs.execute("select * from St_data ")  
7:  result = curs.fetchmany(3)  
8:  for x in result:  
9:    i=i+1  
10:    print(i,x)  
11:  conn.close()  

Read data is in form of "list of tuple(s)", i.e. tuples as an element of list

" select * " means select data of all  columns for a row 

Always remember to close the connection before closing or quitting your software

 conn.close()  

Hope you all like this post and I wish this proves useful to you.
Keep sharing & give your views about post in comments.
Comments are always welcomed for better improvement.

Thanks :)