PYnative

Python Programming

  • Tutorials
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks

Python Select from PostgreSQL Table

Last updated on June 9, 2020

TweetF  sharein  shareP  Pin

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.

Further reading

  • Try to solve Python Database Exercise
  • Also, read Python PostgreSQL Tutorial (Complete Guide)

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.

PostgreSQL mobile table
PostgreSQL mobile table

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

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

Use cursor.execute() to run a query then use.

  • 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

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.
fetchmany() returns fewer rows if the table contains the less number of rows specified by SIZE argument.

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.

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

TweetF  sharein  shareP  Pin

Is this article/website helpful?

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!

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
Show All Exercises

 Show All Quizzes  

Keep Reading Python

Python Input & Output Python MySQL Python PostgreSQL Python SQLite Python JSON Python Quizzes Python Exercises Python Generate random data

Leave a Reply Cancel reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your code </pre>

8 Comments

Practice Python


Practice Python using our 15+ Free Topic-specific Exercises and Quizzes

All exercises and Quizzes are tested on Python 3

Exercises
Quizzes

 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 Input and Output Python MySQL Python PostgreSQL Python SQLite Python JSON Python Quizzes Python Exercises Python Generate random data
TweetF  sharein  shareP  Pin

About PYnative

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

Python

  • Python Tutorials
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow PYnative

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter   Facebook
  • RSS | Sitemap

Legal Stuff

  • About Us
  • Privacy Policy
  • Cookie Policy
  • Terms Of Use
  • Contact Us
DMCA.com Protection Status

Copyright © 2018-2021 · [pynative.com]

This website uses cookies to ensure you get the best experience on our website.Privacy PolicyGot it!