- Python & MySQL Tutorial
- Python & MySQL - Home
- Python & MySQL - Overview
- Python & MySQL - Environment Setup
- Python & MySQL Examples
- Python & MySQL - Connect Database
- Python & MySQL - Create Database
- Python & MySQL - Drop Database
- Python & MySQL - Select Database
- Python & MySQL - Create Tables
- Python & MySQL - Drop Tables
- Python & MySQL - Insert Records
- Python & MySQL - Select Records
- Python & MySQL - Update Records
- Python & MySQL - Delete Records
- Python & MySQL - Where Clause
- Python & MySQL - Like Clause
- Python & MySQL - Sorting Data
- Python & MySQL - Using Joins
- Python & MySQL - Performing Transactions
- Python & MySQL - Handling Errors
- Python & MySQL Useful Resources
- Python & MySQL - Quick Guide
- Python & MySQL - Useful Resources
- Python & MySQL - Discussion
Python MySQL - Join
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') ]
Advertisements