Python PostgreSQL – Select Data

In this article, we are going to see how to use select data using Python in PostgreSQL and psycopg2.

Installation

Open the command prompt and write the command given below.

pip install psycopg2

SELECT statement is used to retrieve the required details of an existing table in PostgreSQL. The data that is returned is stored in a result table that is called the result-set. Data retrieval using select command is limited to only the number of columns specified. If we want to retrieve all columns then we use (*).

Syntax:

Query to select all details of the table: SELECT * FROM table_name

Query to select some specific details of the table: SELECT column_name1, column_name2,….FROM table_name

Table demonstration of SELECT commands:

Example 1: Display all the data in the table.

Python3

import psycopg2

# establishing the connection
conn = psycopg2.connect(
   database="test",
    user='postgres',
    password='password',
    host='localhost',
    port= '5432'
)

# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()

sql = '''CREATE TABLE WORKER(
   ID BIGSERIAL NOT NULL PRIMARY KEY,
   NAME VARCHAR(100) NOT NULL,
   COUNTRY VARCHAR(50) NOT NULL,
   AGE INT,
   SALARY FLOAT
)'''
cursor.execute(sql)

# Inserting values into the table
insert_stmt = "INSERT INTO WORKER (NAME, COUNTRY,\
AGE, SALARY) VALUES (%s, %s, %s, %s)"
data = [('Krishna', 'India', 19,2000),
   ('Harry', 'USA', 20,7000),
   ('Malang', 'Nepal', 25, 5000),
   ('Apple', 'London', 26, 2000),
   ('Vishnu', 'India', 29,2000),
   ('Frank', 'UAE', 21,7000),
   ('Master', 'USA', 25, 5000),
   ('Montu', 'India', 26, 2000),
        ]
cursor.executemany(insert_stmt, data)

# Display whole table
cursor.execute("SELECT * FROM WORKER")
print(cursor.fetchall())

# Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output:

[(‘Krishna’, ‘India’, 19,2000),(‘Harry’, ‘USA’, 20,7000),(‘Malang’, ‘Nepal’, 25, 5000), (‘Apple’, ‘London’, 26, 2000),(‘Vishnu’, ‘India’, 29,2000),(‘Frank’, ‘UAE’, 21,7000), (‘Master’, ‘USA’, 25, 5000),(‘Montu’, ‘India’, 26, 2000)]

Example 2: Return some specific details of the table.

Python3

import psycopg2

# establishing the connection
conn = psycopg2.connect(
   database="test",
    user='postgres',
    password='password',
    host='localhost',
    port= '5432'
)

# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()

sql = '''CREATE TABLE WORKER(
   ID BIGSERIAL NOT NULL PRIMARY KEY,
   NAME VARCHAR(100) NOT NULL,
   COUNTRY VARCHAR(50) NOT NULL,
   AGE INT,
   SALARY FLOAT
)'''
cursor.execute(sql)

# Inserting values into the table
insert_stmt = "INSERT INTO WORKER (NAME, COUNTRY,\
AGE, SALARY) VALUES (%s, %s, %s, %s)"
data = [('Krishna', 'India', 19,2000),
   ('Harry', 'USA', 20,7000),
   ('Malang', 'Nepal', 25, 5000),
   ('Apple', 'London', 26, 2000),
   ('Vishnu', 'India', 29,2000),
   ('Frank', 'UAE', 21,7000),
   ('Master', 'USA', 25, 5000),
   ('Montu', 'India', 26, 2000),
        ]
cursor.executemany(insert_stmt, data)

# Retrieving only NAME and SALARY FROM WORKER
cursor.execute("SELECT NAME, COUNTRY from WORKER")
print(cursor.fetchall())

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

[(‘Krishna’, ‘India’), (‘Harry’, ‘USA’), (‘Malang’, ‘Nepal’), (‘Apple’, ‘London’), (‘Vishnu’, ‘India’), (‘Frank’, ‘UAE’), (‘Master’, ‘USA’), (‘Montu’, ‘India’)]