Python SQLite - Join

When you have spanided 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 using the following query −

sqpte> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqpte>

Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table.

sqpte> CREATE TABLE ODIStats (
   First_Name VARCHAR(255),
   Matches INT,
   Runs INT,
   AVG FLOAT,
   Centuries INT,
   HalfCenturies INT
);
sqpte>

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

sqpte> 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      Indi     133      5518   17          27
Jonathan    Trott       Sout     68       2819   4           22
Kumara      Sangakkara  Sril     404      14234  25          93
Virat       Kohp       Indi     239      11520  43          54
Rohit       Sharma      Indi     218      8686   24          42
sqpte>

Join Clause Using Python

Following SQLite example, demonstrates the JOIN clause using python −

import sqpte3
#Connecting to sqpte
conn = sqpte3.connect( example.db )

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving data
sql =    SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID   

#Executing the query
cursor.execute(sql)

#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, 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 )
]