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 usingfetchmany()andfetchone(). - Use Python variables in the where clause of a PostgreSQL SELECT query to pass dynamic values.
Further Reading:
Table of contents
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.
Steps to execute a PostgreSQL SELECT query from Python
How to Select from a PostgreSQL table using Python
- Connect to PostgreSQL from Python
Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using Psycopg2 module.
- 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. - Get Cursor Object from Connection
Next, use a
connection.cursor()method to create a Psycopg2 cursor object. This method creates a newpsycopg2.extensions.cursorobject. - Execute the SELECT query using a execute() method
Execute the select query using the
cursor.execute()method. - 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. - Iterate each row
Iterate a row list using a
forloop and access each row individually (Access each row’s column data using a column name or index number.) - Close the cursor object and database connection object
use
cursor.clsoe()andconnection.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.
Hi Vishal,
Your examples are very usable. Can map to need.
Hey Vishal! Thanks a lot. I’m writing my first Python script connecting my PostgreSQL DB. Your post was very useful.
Is there any way I could retrieve the column names at the same time to use them in the script, as row[0], [1] and [2] isn’t very clear for who hasn’t the model.
Thanks!
got my answer … just need to include this:
from psycopg2.extras import NamedTupleCursor def connect_db(): conn = None try: params = config() conn = psycopg2.connect(**params, cursor_factory=NamedTupleCursor)Great!
Hi,
Need an urgent help.
How do I read a column in postgres DB of data type BYTEA and encoding of compressed json using PYTHON? I am using psycopg2 to connect to postgresql.
Hi Vishal,
I want to thank you for this article. A small question though, how do you do the same with django?
Please help me when you get time.
Thanks for reading and I’m looking forward to your reply.
Hey Haron Kibet, Soon I will add how to use PostgreSQL with Django and let you know
how to extract a single column from postgress using python
Hi Nikita, Use fetchall and select column by index from rowset