Python Select from PostgreSQL Table using Psycopg2

This article will introduce you to the use of the psycopg2 module which is used to connect to a PostgreSQL database from Python. We will look over how to establish a connection to a database, create a cursor object to execute DBMS SQL statements, execute a SELECT statement to retrieve the data from a table, and create a loop through the rows which are returned by the SELECT statement to process the following data.

After that, we will also cover how to close the cursor and connection when we are finished required operations with the database. We can also perform fetchall operation using psycopg2 library of Python for that we can use fetchall() function to fetch the data from a table in the database and return it in the form of a tuple and practice various operations on the fetched data.

Required Modules

psycopg2:  It is a most useful module to connect the database of PostgreSQL, because of its simple connection method to connect database, it becomes much handier to play with the database using this module.

pip install psycopg2 

Steps to use SELECT operations on PostgreSQL Table 

Step 1: Setup  psycopg2 Python library 

Create a Python file and then import the library given below in it.

Python

import psycopg2

Step 2: Form a connection with the database of PostgreSQL

To use the database and have access to the data for that purpose we need to form a connection with PostgreSQL using the code is given below.

Python

#forming the connection

conn = psycopg2.connect(
    database="postgres",#enter your database name
    user='postgres',#enter your postgres username
    password='123456789',#enter your password
    host='localhost',#enter your host name
    port='5432'#port number
)
# Creating a cursor object with cursor() method

cursor = conn.cursor()

Step 3: Creating a database

There are various methods to create a database in PostgreSQL, you can refer to this article to create your own database in PostgreSQL. Here we use an SQL command CREATE DATABASE database name, it will create a database name as database name so we can use it in further steps.

Python3

# Create the database
cursor.execute("CREATE DATABASE databasename")

# Committing(saving) the changes
conn.commit()

# Close the cursor and connection
cursor.close()
conn.close()

Step 4: Creating a table using SQL Query

To put data inside the database we need a table that holds all the values and by that, we can access the data in the table using different SQL queries. So, Here we create a table called articals for the publisher of books, which includes five attributes publisher_ID, publisher_name,publisher_estd, publisher_location, and publisher_type with various constraints. To execute the given SQL command we use execute() function and to save all the changes successfully in the database we use the commit() function. We can do that using the code given below.

Python3

# table creation  using sql command

sql = '''CREATE TABLE articals(
                publisher_id SERIAL PRIMARY KEY,
                publisher_name VARCHAR(255) NOT NULL,
                publisher_estd INT,
                publsiher_location VARCHAR(255),
                publsiher_type VARCHAR(255)
)'''
cursor.execute(sql)
print("Table created successfully")
conn.commit()

Step 5: Insert the values  in the table

With the help of the INSERT INTO query, we insert some of the values into the table created before the name as articals. We provide some dummy data only for testing purposes but you can also prefer the data of your own. After that, we run a for loop to apply the insert operation query on each data. We can also use this method whenever the submission of forms in the website is done using the flask route function, by creating a route function including the commands of code given above for insertion purposes.

Note: while inserting data, you should use placeholders (%s) for the given values and pass the actual values as a tuple in the second argument of the execute() function. reason for that is to prevent SQL injection attacks.

Python3

# Inserting the data into articals table

postgres_insert_query = """ INSERT INTO articals(publisher_id,
publisher_name, publisher_estd, publsiher_location, publsiher_type)
VALUES (%s,%s,%s,%s,%s)"""
record_to_insert = [(1, 'Packt', 1950,
                     'chennai', 'books'),
                    (22, 'Springer', 1950,
                     'chennai', 'books'),
                    (23, 'Springer', 1950,
                     'chennai', 'articles'),
                    (54, 'Oxford', 1950,
                     'chennai', 'all'),
                    (52, 'MIT', 1950,
                     'chennai', 'books'),
                    (10, 'RCT', 1992,
                     'Mumbai', 'all'),
                    (6, 'ICT', 1995,
                     'Delhi', 'article'),
                    (7, 'PICT', 1955,
                     'Pune', 'article')
                    ]
for i in record_to_insert:
    cursor.execute(postgres_insert_query, i)
    conn.commit()
    count = cursor.rowcount
print(count, "Record inserted successfully \
    into publisher table")

Step 6: Fetch data from the table using the SELECT statement & fetchall() function.

To fetch data from the database we have a function in the psycopg2 library called fetchall() by using this particular function we can have access to the data in the table.

Example 1: select * from articals

With the help of this select operation, we can get access to every single attribute within the data inside that attribute of the articals table. In this statement (*) is used to indicate all means to select all the data from the articals table and return it. To execute the given SQL command we use execute() function with the help of the Cursor() object. Then fetchall() function fetches only the data according to the currently executed SQL Query provided by the user and returns the values in form of a tuple and then we simply print it using the print statement.

Python3

# Fetching Data From articals Table

postgreSQL_select_Query = "select * from articals"

cursor.execute(postgreSQL_select_Query)

print("Selecting rows from publisher table using cursor.fetchall")

publisher_records = cursor.fetchall()

Output:

Figure : output of eg.1)

Example 2: select publisher_ID,publisher_name from articals where publisher_location = ‘Pune’ 

 In this select operation, we use the where clause here we select publisher_ID and publisher_name attributes. Then we apply a condition on both the attributes using the where clause as 
publisher_location = ‘Pune’ this condition state that (from publisher_ID and publisher_name attributes select the ID and name whose location is Pune). It only displays the data which satisfies the given condition provided by the where clause. After that, we can call fetchall() function which fetches all data that matches the condition and then print it.

Python3

postgreSQL_select_Query = "select publisher_ID, publisher_name from articals where publisher_locations='Pune'"

cursor.execute(postgreSQL_select_Query)

publisher_records = cursor.fetchall()

print(publisher_records)

Output:

Figure : output of eg. 2)

Example 3: select * from articals where publisher_name  LIKE ‘S%’ 

In this select operation, we use the LIKE operator where we use (*) to select all the data from the articals table. Then we apply the condition on the publisher_name attribute as LIKE ‘S%’ To fetch records whose publisher_name starts with the letter S. The like operator is used to match a string. Where The ‘%'(wildcard) can signify the later characters here which can be of any length and value. After that, we again call fetchall() function which fetches all data that satisfies the condition and then prints it.

Python3

postgreSQL_select_Query = "select * from articals where publisher_name LIKE 'S%'"

cursor.execute(postgreSQL_select_Query)

publisher_records = cursor.fetchall()

print(publisher_records)

Output:

Figure : output of eg. 3)

Example 4: select distinct publisher_estd from articles 

This query will return all the unique combinations of rows in the following table with fields publisher_estd.In this select operation, we use the distinct keyword which provides unique values from the column on which we apply this keyword. After that, we can call the fetchall() function which fetches all data and then prints it.

Python3

postgreSQL_select_Query = "select distinct publisher_estd from articals"

cursor.execute(postgreSQL_select_Query)

publisher_records = cursor.fetchall()

print(publisher_records)

Output:

Figure : output of  eg.4)