In this article, we will discuss how we can count the number of rows of a given SQLite Table using Python. We will be using the cursor_obj.fetchall() method to do the same. This method fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch.
To create the database, we will execute the following code:
Python3
import sqlite3 # Connecting to sqlite # connection object connection_obj = sqlite3.connect('geek.db') # cursor object cursor_obj = connection_obj.cursor() # Drop the GEEK table if already exists. cursor_obj.execute("DROP TABLE IF EXISTS GEEK") # Creating table table = """ CREATE TABLE GEEK ( Email VARCHAR(255) NOT NULL, Name CHAR(25) NOT NULL, Score INT ); """ cursor_obj.execute(table) # inserting data into geek table connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk1@gmail.com","Geek1",25)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk2@gmail.com","Geek2",15)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk3@gmail.com","Geek3",36)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk4@gmail.com","Geek4",27)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk5@gmail.com","Geek5",40)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk6@gmail.com","Geek6",14)""") connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk7@gmail.com","Geek7",10)""") connection_obj.commit() # Close the connection connection_obj.close() |
Output:
So by finding the length of this list which is return by fetchall(), we get the count of the total number of rows.
Python3
import sqlite3 # Connecting to sqlite # connection object connection_obj = sqlite3.connect('geek.db') # cursor object cursor_obj = connection_obj.cursor() print("Count of Rows") cursor_obj.execute("SELECT * FROM GEEK") print(len(cursor_obj.fetchall())) connection_obj.commit() # Close the connection connection_obj.close() |
Output:
