World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
USA(+256704300651)Top Powerful Love Spells That Really Work By Powerful Revenge Spell Caster in# Canada, #Australia #,UK,//USA Black magic IN >@>USA,/ UK,/ Kuwait,/ Germany, +256704300651--- …Extremely Powerful Death Spells Caster To Kill An Enemy With No Evidence / Ex Husband How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? +256704300651- Psychic lost love Spell Caster // Divorce Spell // Marriage Proposal A SPELL CASTER/╬ ₩௹]+256704300651₩௹]╬MAMA ZAMIE A TRADITIONAL HEALER /SANGOMA AND A SPIRITUAL HEALER in BELGIUM, +256704300651 == WASHINGTON DC// TOP DEATH SPELLS @POWERFUL REVENGE DEATH SPELL CASTER IN USA,CANADA, UK, CALIFONIA, MEXICO, IRELAND, NY, CANADA, LONDON, SWEDEN, BOTSWANA Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) FORTUNER TELLER-(( +256704300651))- Most Effective death revenge Spells Caster USA, UK, BAHAMAS, BAHRAIN, NEW ZEALAND,SPAIN AUSTRALIA SOUTH SUDAN Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Live Masterclass on : "How Machine Get Trained in Machine Learning?"
Jun 1st (5:32 PM) 515 Registered
Python PostgreSQL - Select Data
Neha Kumawat
a year ago
Table of Contents
- Introduction
- Retrieving Data Using Python
Introduction
You can retrieve the contents of an existing table in PostgreSQL using the SELECT statement. At this statement, you need to specify the name of the table and, it returns its contents in tabular format which is known as result set.
Syntax:
Following is the syntax of the SELECT statement in PostgreSQL −
SELECT column1, column2, columnN FROM table_name;
Example
Assume we have created a table with name CRICKETERS using the following query −
postgres=# CREATE TABLE CRICKETERS (
First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int,
Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#
And if we have inserted 5 records in to it using INSERT statements as −
postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1
Following SELECT query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table.
postgres=# SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
first_name | last_name | country
------------+------------+-------------
Shikhar | Dhawan | India
Jonathan | Trott | SouthAfrica
Kumara | Sangakkara | Srilanka
Virat | Kohli | India
Rohit | Sharma | India
(5 rows)
If you want to retrieve all the columns of each record you need to replace the names of the columns with "⚹" as shown below −
postgres=# SELECT * FROM CRICKETERS;
first_name | last_name | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar | Dhawan | 33 | Delhi | India
Jonathan | Trott | 38 | CapeTown | SouthAfrica
Kumara | Sangakkara | 41 | Matale | Srilanka
Virat | Kohli | 30 | Delhi | India
Rohit | Sharma | 32 | Nagpur | India
(5 rows)
postgres=#
Retrieving Data Using Python
READ Operation on any database means to fetch some useful information from the database. You can fetch data from PostgreSQL using the fetch() method provided by the psycopg2.
The Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,
- The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows, it returns the remaining ones).
The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows, it returns the remaining ones).
- The fetchone() method fetches the next row in the result of a query and returns it as a tuple.
The fetchone() method fetches the next row in the result of a query and returns it as a tuple.
Note − A result set is an object that is returned when a cursor object is used to query a table.
Example:
The following Python program connects to a database named mydb of PostgreSQL and retrieves all the records from a table named EMPLOYEE.
import psycopg2
#establishing the connection
conn = psycopg2.connect(
database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Setting auto commit false
conn.autocommit = True
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving data
cursor.execute('''SELECT * from EMPLOYEE''')
#Fetching 1st row from the table
result = cursor.fetchone();
print(result)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()
Output:
('Ramya', 'Rama priya', 27, 'F', 9000.0)
[
('Vinay', 'Battacharya', 20, 'M', 6000.0),
('Sharukh', 'Sheik', 25, 'M', 8300.0),
('Sarmista', 'Sharma', 26, 'F', 10000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Like the Blog, then Share it with your friends and colleagues to make this AI community stronger.
To learn more about nuances of Artificial Intelligence, Python Programming, Deep Learning, Data Science and Machine Learning, visit our insideAIML blog page.
Keep Learning. Keep Growing.