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="Lazyroar", 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
