World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
USA(+256704300651)Top Powerful Love Spells That Really Work By Powerful Revenge Spell Caster in# Canada, #Australia #,UK,//USA Black magic IN >@>USA,/ UK,/ Kuwait,/ Germany, +256704300651 == WASHINGTON DC// TOP DEATH SPELLS @POWERFUL REVENGE DEATH SPELL CASTER IN USA,CANADA, UK, CALIFONIA, MEXICO, IRELAND, NY, CANADA, LONDON, SWEDEN, BOTSWANA How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? +256704300651- Psychic lost love Spell Caster // Divorce Spell // Marriage Proposal ((+256704300651}} INSTANT DEATH SPELL CASTER / REVENGE SPELL/ VOODOO A SPELL CASTER/╬ ₩௹]+256704300651₩௹]╬MAMA ZAMIE A TRADITIONAL HEALER /SANGOMA AND A SPIRITUAL HEALER in BELGIUM, Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) +256704300651 NY ( ITALY /UK/ SWEDEN/USA )) Black Magic Instant Death Spell Caster Lubbock Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python MySQL - Join
Neha Kumawat
a year ago
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.