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.
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 connectionconn = psycopg2.connect( database="test", user='postgres', password='password', host='localhost', port= '5432') # Creating a cursor object using the cursor()# methodcursor = 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 tableinsert_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 tablecursor.execute("SELECT * FROM WORKER")print(cursor.fetchall()) # Commit your changes in the databaseconn.commit() #Closing the connectionconn.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 connectionconn = psycopg2.connect( database="test", user='postgres', password='password', host='localhost', port= '5432') # Creating a cursor object using the cursor()# methodcursor = 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 tableinsert_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 WORKERcursor.execute("SELECT NAME, COUNTRY from WORKER")print(cursor.fetchall()) # Commit your changes in the databaseconn.commit() # Closing the connectionconn.close() |
Output:
[(‘Krishna’, ‘India’), (‘Harry’, ‘USA’), (‘Malang’, ‘Nepal’), (‘Apple’, ‘London’), (‘Vishnu’, ‘India’), (‘Frank’, ‘UAE’), (‘Master’, ‘USA’), (‘Montu’, ‘India’)]
