How to Count the Number of Rows of SQLite Table in Python?

Python is a popular high-level, interpreted programming language for artificial intelligence, data analysis, and web development. It provides a range of libraries and modules for various tasks such as data manipulation, data visualization, and database management. One such library is SQLite which is a lightweight and efficient embedded database engine that supports SQL syntax.

In this essay, we will discuss how to count the rows of SQLite Table in Python.

The article will cover:

How to Count the Rows of SQLite Table in Python
How to Create a Database in SQLite using Python
How to Create a Table in SQLite using Python
How to Insert Values in Table in SQLite using Python
How to Count the Number of Rows in SQLite using Python
How to Close the Connection in SQLite using Python

1: How to Count the Rows of SQLite Table in Python?

You can count the rows of an SQLite table in Python by following the steps given below in Python IDE called PyCharm.

Step 1: Open the PyCharm IDE, create a project, and click on File.

Step 2: Click on Settings.

Step 3: Find and select Python Interpreter under Project <projectname>, then click on +.

Step 4: Search db-sqlite3 in the search bar.

Step 5: Click on Install Package and the installation starts.

2: How to Create a Database in SQLite using Python?

After installing the library, the next step is to create a database. You can use the following code in Python to create a database:

import sqlite3
conn = sqlite3.connect('example.db')

This code creates a connection to the SQLite database called example.db. If the database already exists, it will connect to it; if it does not exist, it will create one.

3: How to Create a Table in SQLite using Python?

A database can have tables added to it after it has been created. Data that needs to be sorted and structured is kept in tables. The Python code below can be used to construct a table:

c = conn.cursor()
c.execute('''CREATE TABLE products (date text, symbol text, price real)''')
conn.commit()

This code creates a table called products in the example.db database. It has three columns: date, symbol, and price. The columns have different data types: date is a text type, the symbol is also a text type, and the price is a real type.

4: How to Insert Values in Table in SQLite using Python?

You can add data to a table after you’ve built it. You can use the following code to insert data into the table:

c.execute("INSERT INTO products VALUES ('2022-06-14', 'GOOG', 1923.88)")
conn.commit()

This code inserts a row of data into the products table. The row contains the date 2022-06-14, the symbol GOOG, and the price 1923.88.

5: How to Count the Number of Rows in SQLite using Python?

To count the number of rows, we will employ the c.fetchall() function. This method retrieves every row from a query result. All of the rows are given back as a list of tuples. If there are no records to retrieve, an empty list is returned.

print("Count of Rows")

c.execute("SELECT * FROM products")

print(len(c.fetchall()))

6: How to Close the Connection in SQLite using Python?

You can close the connection by the command:

conn.close()

Let’s look at the code example performing all of the tasks mentioned above.

import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute('''CREATE TABLE products (date text, symbol text, price real)''')

conn.commit()

c.execute("INSERT INTO products VALUES ('2022-06-14', 'GOOG', 1923.88)")

print("Count of Rows")

c.execute("SELECT * FROM products")

print(len(c.fetchall()))

conn.commit()

conn.close()

The above code creates a connection with an SQLite Database, creates a table in it, inserts values in it, retrieves values from it, and then closes the connection.

Output

Conclusion

Python is a well-liked programming language used for AI, data analysis, and web development. SQLite is a lightweight embedded database engine that supports SQL syntax. By following the provided steps, you can use Python to create and manipulate SQLite databases, including counting the rows in a table.