Python PostgreSQL - Joins

When you have divided the data in two tables you can fetch combined records from these two tables using Joins.

Example

Assume we have created a table with name CRICKETERS and inserted 5 records into it as shown below −

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
postgres=# insert into CRICKETERS values (
   'Shikhar', 'Dhawan', 33, 'Delhi', 'India'
);
postgres=# insert into CRICKETERS values (
   'Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica'
);
postgres=# insert into CRICKETERS values (
   'Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka'
);
postgres=# insert into CRICKETERS values (
   'Virat', 'Kohli', 30, 'Delhi', 'India'
);
postgres=# insert into CRICKETERS values (
   'Rohit', 'Sharma', 32, 'Nagpur', 'India'
);

And, if we have created another table with name OdiStats and inserted 5 records into it as −

postgres=# CREATE TABLE ODIStats (
   First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, 
   Centuries INT, HalfCenturies INT
);
postgres=# insert into OdiStats values ('Shikhar', 133, 5518, 44.5, 17, 27);
postgres=# insert into OdiStats values ('Jonathan', 68, 2819, 51.25, 4, 22);
postgres=# insert into OdiStats values ('Kumara', 404, 14234, 41.99, 25, 93);
postgres=# insert into OdiStats values ('Virat', 239, 11520, 60.31, 43, 54);
postgres=# insert into OdiStats values ('Rohit', 218, 8686, 48.53, 24, 42);

Following statement retrieves data combining the values in these two tables −

postgres=# SELECT
Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country,
OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies
from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name;
first_name  | last_name  | country     | matches | runs  | centuries | halfcenturies
------------+------------+-------------+---------+-------+-----------+---------------
Shikhar     | Dhawan     | India       | 133     | 5518  | 17        | 27
Jonathan    | Trott      | SouthAfrica | 68      | 2819  | 4         | 22
Kumara      | Sangakkara | Srilanka    | 404     | 14234 | 25        | 93
Virat       | Kohli      | India       | 239     | 11520 | 43        | 54
Rohit       | Sharma     | India       | 218     | 8686  | 24        | 42
(5 rows)
postgres=#

Example - Joins using python

When you have divided the data in two tables you can fetch combined records from these two tables using Joins.

Following python program demonstrates the usage of the JOIN clause −

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()

#Dropping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
)'''
cursor.execute(sql)

sql ='''CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   CITY CHAR(20)
)'''
cursor.execute(sql)

# Preparing SQL queries to INSERT a record into the database.
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,
   INCOME, CONTACT) VALUES ('Ramya', 'Rama priya', 27, 'F', 9000, 101)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,
   INCOME, CONTACT) VALUES ('Vinay', 'Battacharya', 20, 'M', 6000, 102)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,
   INCOME, CONTACT) VALUES ('Sharukh', 'Sheik', 25, 'M', 8300, 103)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,
   INCOME, CONTACT) VALUES ('Sarmista', 'Sharma', 26, 'F', 10000, 104)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,
   INCOME, CONTACT) VALUES ('Tripthi', 'Mishra', 24, 'F', 6000, 105)''')

cursor.execute('''INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES 
   (101, 'Krishna@mymail.com', 'Hyderabad'), 
   (102, 'Raja@mymail.com', 'Vishakhapatnam'), 
   (103, 'Krishna@mymail.com', 'Pune'), 
   (104, 'Raja@mymail.com', 'Mumbai')''')

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

#Retrieving single row
sql = '''SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID'''

#Executing the query
cursor.execute(sql)

#Fetching all rows 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, 101, 101, 'Krishna@mymail.com', 'Hyderabad'), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102, 'Raja@mymail.com', 'Vishakhapatnam'), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, 'Krishna@mymail.com ', 'Pune'), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, 'Raja@mymail.com', 'Mumbai')]