Python MySQL - Join

Neha Kumawat

5 months ago

Python MySQL
Table of Content
  • Example
  • MYSQL JOIN Using Python
  • Example
  • Output
When you have divided the data in two tables you can fetch combined records from these two tables using Joins.

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);
Query OK, 0 rows affected (0.36 sec)
INSERT INTO Employee VALUES 
   ('Ramya', 'Rama Priya', 27, 'F', 9000, 101), 
   ('Vinay', 'Bhattacharya', 20, 'M', 6000, 102), 
   ('Sharukh', 'Sheik', 25, 'M', 8300, 103), 
   ('Sarmista', 'Sharma', 26, 'F', 10000, 104), 
   ('Trupthi', 'Mishra', 24, 'F', 6000, 105);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
Then, if we have created another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);
Query OK, 0 rows affected (0.49 sec)

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'); 
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
Following statement retrieves data combining the values in these two tables −

mysql> SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID;
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| FIRST_NAME | LAST_NAME    | AGE  | SEX  | INCOME | CONTACT | ID  | EMAIL              | PHONE | CITY           |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| Ramya      | Rama Priya   | 27   | F    | 9000   | 101     | 101 | Krishna@mymail.com | NULL  | Hyderabad      |
| Vinay      | Bhattacharya | 20   | M    | 6000   | 102     | 102 | Raja@mymail.com    | NULL  | Vishakhapatnam |
| Sharukh    | Sheik        | 25   | M    | 8300   | 103     | 103 | Krishna@mymail.com | NULL  | Pune           |
| Sarmista   | Sharma       | 26   | F    | 10000  | 104     | 104 | Raja@mymail.com    | NULL  | Mumbai         |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
4 rows in set (0.00 sec)

MySQL Join Using Python

      Following example retrieves data from the above two tables combined by contact column of the EMPLOYEE table and ID column of the CONTACT table.

Example


import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

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

#Retrieving single row
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)

#Closing the connection
conn.close()

Output


[
   ('Krishna', 'Sharma', 26, 'M', 2000, 101, 101, 'Krishna@mymail.com', 9848022338, 'Hyderabad'), 
   ('Raj', 'Kandukuri', 20, 'M', 7000, 102, 102, 'Raja@mymail.com', 9848022339, 'Vishakhapatnam'), 
   ('Ramya', 'Ramapriya', 29, 'F', 5000, 103, 103, 'Krishna@mymail.com', 9848022337, 'Pune'), 
   ('Mac', 'Mohan', 26, 'M', 2000, 104, 104, 'Raja@mymail.com', 9848022330, 'Mumbai')
]
Liked what you read? Then don’t break the spree. Visit our insideAIML blog page to read more awesome articles. 
Or if you are into videos, then we have an amazing Youtube channel as well. Visit our InsideAIML Youtube Page to learn all about Artificial Intelligence, Deep Learning, Data Science and Machine Learning. 
Keep Learning. Keep Growing.

Submit Review