Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Python PostgreSQL – Select Data

  • Last Updated : 23 Aug, 2021

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.

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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




My Personal Notes arrow_drop_up
Recommended Articles
Page :