Python Sqlite - 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 using the following query −

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

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

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

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

sqlite> 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      Kohli        Indi     239        11520         43         54
Rohit      Sharma       Indi     218        8686          24         42
sqlite>

Join clause using python

Following SQLite example, demonstrates the JOIN clause using python −

main.py

import sqlite3

#Connecting to sqlite
conn = sqlite3.connect('example.db')

#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 data
sql = '''SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.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')]