Open In App

Python PostgreSQL – Select Data

Improve
Improve
Like Article
Like
Save
Share
Report

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’)]



Last Updated : 09 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads