PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Select from PostgreSQL Table using Psycopg2

Python Select from PostgreSQL Table using Psycopg2

Updated on: March 9, 2021 | 10 Comments

In this lesson, you will learn to execute a PostgreSQL SELECT query from Python using the Psycopg2 module.

You’ll learn the following PostgreSQL SELECT operations from Python:

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

Further Reading:

  • Solve Python PostgreSQL Exercise
  • Read Python PostgreSQL Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • Steps to execute a PostgreSQL SELECT query from Python
    • Example to retrieve a row from PostgreSQL Table using fetchall()
  • Use Python variable as parameters in PostgreSQL Select Query
  • Retrieve a limited number of rows from the PostgreSQL table
    • Retrieve a single row from the PostgreSQL query result
  • Next Steps:

Prerequisites

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 a table is not present in your PostgreSQL server, you can refer to our article to create a PostgreSQL table from Python.

PostgreSQL mobile table
PostgreSQL mobile table

Steps to execute a PostgreSQL SELECT query from Python

How to Select from a PostgreSQL table using Python

  1. Connect to PostgreSQL from Python

    Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using Psycopg2 module.

  2. Define a PostgreSQL SELECT Query

    Next, prepare a SQL SELECT query to fetch rows from a table. You can select all or limited rows based on your need. If the where condition is used, then it decides the number of rows to fetch.
    For example, SELECT col1, col2,…colnN FROM postgresql_table WHERE id = 5;. This will return row number 5.

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a Psycopg2 cursor object. This method creates a new psycopg2.extensions.cursor object.

  4. Execute the SELECT query using a execute() method

    Execute the select query using the cursor.execute() method.

  5. Extract all rows from a result

    After successfully executing a Select operation, Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of rows.

  6. Iterate each row

    Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column name or index number.)

  7. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Let see the example now.

Example to retrieve a row from PostgreSQL Table using fetchall()

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

Note: In the above example, we used a cursor.fetchall() to get all the rows of a database table.

Use the following methods of a cursor class to get a different result.

  • cursor.fetchall() to fetch all rows.
  • cursor.fetchone() to fetch single row.
  • cursor.fetchmany(SIZE) to fetch limited rows

Read more: Python cursor’s fetchall(), fetchmany(), fetchone() to read records from database table

Use Python variable as parameters in PostgreSQL Select Query

Most of the time, we need to pass Python variables as parameters to PostgreSQL queries to get the result. For example, the application can give 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.

import psycopg2

def get_mobile_details(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")

get_mobile_details(2)
get_mobile_details(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 the PostgreSQL table

In most situations, retrieving all of the rows from a table using Python 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.

Read fetchmany() in detail

Example

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("Fetching 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

Retrieve a single row from the PostgreSQL query result

  • 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 at a time.

Example

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python PostgreSQL

  • Python PostgreSQL Guide
  • Python PostgreSQL Select
  • Python PostgreSQL CRUD
  • Python PostgreSQL call Function
  • Python PostgreSQL Transactions
  • Python PostgreSQL Connection Pool
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com