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.
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. AProgrammingError
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()
andfetchmany()
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.