Get psycopg2 count(*) number of results

In this article, we are going to see how to get psycopg2 count(*) number of results. 

psycopg2 count(*) returns the number of rows from a database table holding some specific conditions. If no condition is given then it returns the total number of tuples present in the relation. 

Syntax:

SELECT  COUNT(*)  FROM  table_name;  # to return total no. of rows in the table

SELECT  COUNT(*)  FROM  table_name  WHERE condition;  # to return no. of rows with some specified condition

Let’s see the following syntax in PostgreSql prompt:

First of all, we will import psycopg2 module that will deal with postgreSQL database, and then a database connection is established. Then we will create a cursor object that allows Python code to execute PostgreSQL command in a database session. We will then write a query to execute the total number of rows with specific details. 

For ex- in the code given below, we are writing the first query to return the total no. of rows present in the table and the second query is written to return the total number of rows where the price name is 1.99. Finally, the number of rows has been fetched and displayed.

Python3

# importing psycopg2
import psycopg2

conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="root",
    host="localhost",
    port="5432"
)

# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()

# query to count total number of rows
sql = 'SELECT count(*) from products;'
data=[]

# execute the query
cursor.execute(sql,data)
results = cursor.fetchone()

#loop to print all the fetched details
for r in results:
  print(r)
print("Total number of rows in the table:", r)

# query to count number of rows
# where country name is India
sql1 = 'SELECT count(*) from products WHERE "price" = 1.99;'
data1=['India']

# execute query
cursor.execute(sql1,data1)
result = cursor.fetchone()
for r1 in result:
  print(r1)
print("Total Number of rows where country name is India:",r1)

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

Count number of rows in the cursor