Python Select from PostgreSQL Table

In this article, we will learn how to execute a PostgreSQL SELECT query from a Python application to fetch data from the database table using Psycopg2. Also, learn how to process and use result set returned by SELECT Query.

Goals of this lesson. In this lesson, you’ll learn the following PostgreSQL SELECT operations from Python:

• Retrieve all rows from the PostgreSQL table using fetchall(), and limited rows using fetchmany() and fetchone().
• Use Python variables in the where clause of a PostgreSQL SELECT query to pass dynamic values.

Before we start

Before executing the following programs, please make sure you have the following in place: –

• Username and password that you need to connect PostgreSQL
• PostgreSQL database table from which you want to fetch data.

For this article, I am using a mobile table created in my PostgreSQL database. If no table is present in your PostgreSQL server you can refer to our article to create a PostgreSQL table from Python.

Steps to perform a PostgreSQL SELECT query from Python

• Install psycopg2 using pip.
• Create a PostgreSQL database connection.
• Define the SELECT statement query to fetch data from the PostgreSQL table.
• Execute the SELECT query using a cursor.execute() and get a python ResultSet.
• Iterate over the ResultSet using for loop to get the database fields (columns) from each row.
• Close the cursor and database connection.
• Catch any SQL exceptions that may occur during the process.

Let see the example now.

Python example to retrieve data from PostgreSQL Table using fetchall()

In this example, Let see how to fetch all rows from the database table.

import psycopg2

try:
   connection = psycopg2.connect(user="sysadmin",
                                  password="pynative@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")
   cursor = connection.cursor()
   postgreSQL_select_Query = "select * from mobile"

   cursor.execute(postgreSQL_select_Query)
   print("Selecting rows from mobile table using cursor.fetchall")
   mobile_records = cursor.fetchall() 
   
   print("Print each row and it's columns values")
   for row in mobile_records:
       print("Id = ", row[0], )
       print("Model = ", row[1])
       print("Price  = ", row[2], "\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while fetching data from PostgreSQL", error)

finally:
    #closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Output:

Selecting rows from mobile table using cursor.fetchall
Print each row and it's columns values

Id =  1
Model =  IPhone XS
Price  =  1000.0 

Id =  3
Model =  Google Pixel
Price  =  700.0 

Id =  2
Model =  Samsung Galaxy S9
Price  =  900.0 

Id =  4
Model =  LG
Price  =  800.0 

PostgreSQL connection is closed

Pass Python variable as parameters in PostgreSQL Select Query

Most of the time we need to pass python variables as parameters to SQL queries to get the result. For example, the application can pass any user id to get the user details to handle such requirements we need to use a parameterized query.

A parameterized query is a query in which we use placeholders (%s) for parameters, and the parameter values supplied at execution time. That means parameterized query compiled only once.

Read more on What is a Parameterized Query and its performance benefits. Let see the example now.

import psycopg2

def getMobileDetails(mobileID):
    try:
        connection = psycopg2.connect(user="sysadmin",
                                      password="pynative@#29",
                                      host="127.0.0.1",
                                      port="5432",
                                      database="postgres_db")

        print("Using Python variable in PostgreSQL select Query")
        cursor = connection.cursor()
        postgreSQL_select_Query = "select * from mobile where id = %s"

        cursor.execute(postgreSQL_select_Query, (mobileID,))
        mobile_records = cursor.fetchall()
        for row in mobile_records:
            print("Id = ", row[0], )
            print("Model = ", row[1])
            print("Price  = ", row[2])

    except (Exception, psycopg2.Error) as error:
        print("Error fetching data from PostgreSQL table", error)

    finally:
        # closing database connection
        if (connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed \n")

getMobileDetails(2)
getMobileDetails(3)

Output:

Using Python variable in PostgreSQL select Query
Id =  2
Model =  Samsung Galaxy S9
Price  =  900.0
PostgreSQL connection is closed 

Using Python variable in PostgreSQL select Query
Id =  3
Model =  Google Pixel
Price  =  700.0
PostgreSQL connection is closed

Retrieve a limited number of rows from PostgreSQL table

In most of the situation retrieving all of the rows from a table can be time-consuming if the table contains thousands of rows.

So a better alternative is to retrieve a few rows using a cursor.fetchmany().

Syntax of fetchmany().

cursor.fetchmany([size=cursor.arraysize])

• Here size is the number of rows to be retrieved.
• This method fetches the next set of rows from a query result. fetchmany() method return a list of tuple contains the rows.
• fetchmany returns an empty list when no more rows are available in the table.
  The number of rows to fetch depends on the SIZE argument. A ProgrammingError raised if the previous call to execute*() did not produce any result set or no call issued yet.

Python example to fetch limited rows from PostgreSQL table using a cursor.fetchmany()

import psycopg2

try:
   connection = psycopg2.connect(user="sysadmin",
                                  password="pynative@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")

   print("Selecting rows from mobile table using cursor.fetchall")
   cursor = connection.cursor()
   postgreSQL_select_Query = "select * from mobile"

   cursor.execute(postgreSQL_select_Query)
   mobile_records = cursor.fetchmany(2)
   
   print("Printing 2 rows")
   for row in mobile_records:
       print("Id = ", row[0], )
       print("Model = ", row[1])
       print("Price  = ", row[2], "\n")

   mobile_records = cursor.fetchmany(2)
   
   print("Printing next 2 rows")
   for row in mobile_records:
       print("Id = ", row[0], )
       print("Model = ", row[1])
       print("Price  = ", row[2], "\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while fetching data from PostgreSQL", error)

finally:
    #closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Output:

Selecting rows from mobile table using cursor.fetchall

Printing 2 rows

Id =  1
Model =  IPhone XS
Price  =  1000.0 

Id =  2
Model =  Samsung Galaxy S9
Price  =  900.0 

Printing next 2 rows

Id =  3
Model =  Google Pixel
Price  =  700.0 

Id =  4
Model =  LG
Price  =  800.0 

PostgreSQL connection is closed

Using cursor.fetchone

• Use a cursor.fetchone() to retrieve only a single row from the PostgreSQL table in Python.
• You can also use cursor.fetchone() to fetch the next row of a query result set. This method returns a single tuple.
• It can return a none if no rows are available in the resultset.
• The cursor.fetchall() and fetchmany() method internally uses this method.

Python example to retrieve a single row from PostgreSQL table using cursor.fetchone.

import psycopg2

try:
   connection = psycopg2.connect(user="sysadmin",
                                  password="pynative@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")

   PostgreSQL_select_Query = "select * from mobile"
   cursor = connection.cursor()

   cursor.execute(PostgreSQL_select_Query)

   mobile_records_one = cursor.fetchone()
   print ("Printing first record", mobile_records_one)

   mobile_records_two = cursor.fetchone()
   print("Printing second record", mobile_records_two)

except (Exception, psycopg2.Error) as error :
    print ("Error while getting data from PostgreSQL", error)

finally:
    #closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Output:

Printing first record (1, 'IPhone XS', 1000.0)
Printing second record (2, 'Samsung Galaxy S9', 900.0)
PostgreSQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.