How to Count the Number of Rows of a Given SQLite Table Using Python?

Counting the number of rows in an SQLite table is a common task in database management. Python's built-in sqlite3 module provides seamless tools for this purpose.

In this article, we will explore how to efficiently count rows in an SQLite table using Python, enabling effective data analysis and manipulation.

Prerequisites

Python comes with sqlite3 built-in, so no additional installation is required. Simply import it in your script ?

import sqlite3

Creating a Sample Database

Let's first create a sample database with some data to work with ?

import sqlite3

# Create a sample database
conn = sqlite3.connect(':memory:')  # In-memory database for demo
cursor = conn.cursor()

# Create a sample table
cursor.execute('''CREATE TABLE employees 
                  (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')

# Insert sample data
sample_data = [
    (1, 'Alice', 'Engineering'),
    (2, 'Bob', 'Marketing'),
    (3, 'Charlie', 'Engineering'),
    (4, 'Diana', 'Sales'),
    (5, 'Eve', 'Marketing')
]

cursor.executemany('INSERT INTO employees VALUES (?, ?, ?)', sample_data)
conn.commit()

print("Sample database created with employees table")

Sample database created with employees table

Method 1: Using SELECT COUNT(*)

The most straightforward way to count rows is using the COUNT(*) SQL function ?

import sqlite3

# Create sample database (reusing previous setup)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''CREATE TABLE employees 
                  (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')

sample_data = [
    (1, 'Alice', 'Engineering'),
    (2, 'Bob', 'Marketing'),
    (3, 'Charlie', 'Engineering'),
    (4, 'Diana', 'Sales'),
    (5, 'Eve', 'Marketing')
]
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?)', sample_data)
conn.commit()

# Count total rows
query = "SELECT COUNT(*) FROM employees"
cursor.execute(query)
result = cursor.fetchone()
row_count = result[0]

print(f"Total rows in employees table: {row_count}")

cursor.close()
conn.close()

Total rows in employees table: 5

Method 2: Counting with Conditions

You can also count rows that match specific conditions using the WHERE clause ?

import sqlite3

# Create sample database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''CREATE TABLE employees 
                  (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')

sample_data = [
    (1, 'Alice', 'Engineering'),
    (2, 'Bob', 'Marketing'),
    (3, 'Charlie', 'Engineering'),
    (4, 'Diana', 'Sales'),
    (5, 'Eve', 'Marketing')
]
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?)', sample_data)
conn.commit()

# Count rows with condition
department = 'Engineering'
query = "SELECT COUNT(*) FROM employees WHERE department = ?"
cursor.execute(query, (department,))
result = cursor.fetchone()
engineering_count = result[0]

print(f"Employees in {department}: {engineering_count}")

cursor.close()
conn.close()

Employees in Engineering: 2

Method 3: Using Context Manager

A more Pythonic approach uses context managers to automatically handle connection closing ?

import sqlite3

def count_table_rows(db_name, table_name):
    with sqlite3.connect(db_name) as conn:
        cursor = conn.cursor()
        query = "SELECT COUNT(*) FROM employees"  # Note: table name can't be parameterized
        cursor.execute(query)
        return cursor.fetchone()[0]

# Create sample database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''CREATE TABLE employees 
                  (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')

sample_data = [
    (1, 'Alice', 'Engineering'),
    (2, 'Bob', 'Marketing'),
    (3, 'Charlie', 'Engineering')
]
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?)', sample_data)
conn.commit()
conn.close()

# For demo, we'll use :memory: database differently
with sqlite3.connect(':memory:') as conn:
    cursor = conn.cursor()
    
    cursor.execute('''CREATE TABLE employees 
                      (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')
    
    sample_data = [
        (1, 'Alice', 'Engineering'),
        (2, 'Bob', 'Marketing'),
        (3, 'Charlie', 'Engineering')
    ]
    cursor.executemany('INSERT INTO employees VALUES (?, ?, ?)', sample_data)
    conn.commit()
    
    query = "SELECT COUNT(*) FROM employees"
    cursor.execute(query)
    count = cursor.fetchone()[0]
    
    print(f"Row count using context manager: {count}")

Row count using context manager: 3

Handling Exceptions

Always handle potential database errors gracefully ?

import sqlite3

def safe_count_rows(table_name):
    try:
        with sqlite3.connect(':memory:') as conn:
            cursor = conn.cursor()
            
            # Create and populate table for demo
            cursor.execute('''CREATE TABLE employees 
                              (id INTEGER PRIMARY KEY, name TEXT)''')
            cursor.execute('INSERT INTO employees VALUES (1, "Alice")')
            cursor.execute('INSERT INTO employees VALUES (2, "Bob")')
            conn.commit()
            
            # Count rows - note: table name can't be parameterized safely
            query = f"SELECT COUNT(*) FROM {table_name}"
            cursor.execute(query)
            return cursor.fetchone()[0]
            
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return None
    except Exception as e:
        print(f"Unexpected error: {e}")
        return None

# Test with valid table
count = safe_count_rows('employees')
print(f"Row count: {count}")

# Test with invalid table
count = safe_count_rows('nonexistent_table')
print(f"Row count for invalid table: {count}")

Row count: 2
Database error: no such table: nonexistent_table
Row count for invalid table: None

Comparison of Methods

Conclusion

Counting rows in SQLite tables using Python is straightforward with the sqlite3 module. Use SELECT COUNT(*) for basic counting, add WHERE clauses for conditional counts, and always handle exceptions for robust applications.