How to Combine Rows From Two Or More Tables in Python MySQL


In this example, we will learn how to combine rows from two or more tables in Python MySQL.

Source Code

'''
the "schedule" table:
{id: 1, subject: 'Python', stu_id: 1},
{id: 2, subject: 'Java', stu_id: 2},
{id: 3, subject: 'PHP', stu_id: 3},
{id: 4, subject: 'C', stu_id: 4},
{id: 5, subject: 'JavaScript', stu_id: 5},
{id: 6, subject: 'R', stu_id: 6},
{id: 7, subject: 'C++', stu_id: 0}

the "student" table:
{stu_id: 1, stu_name: 'Emma'},
{stu_id: 2, stu_name: 'May'},
{stu_id: 3, stu_name: 'Leon'},
{stu_id: 4, stu_name: 'Bruce'},
{stu_id: 5, stu_name: 'Anne'},
{stu_id: 6, stu_name: 'Kevin'},
{stu_id: 7, stu_name: 'Amy'},
{stu_id: 8, stu_name: 'Kate'},
{stu_id: 9, stu_name: 'Ben'}
'''

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="yourusername",
    password="yourpassword",
    db="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT schedule.subject, student.stu_name 
       FROM schedule 
       INNER JOIN student 
       ON schedule.stu_id = student.stu_id"

mycursor.execute(sql)

results = mycursor.fetchall()

for row in results:
    print(row)

mydb.close()

Output:

('Python', 'Emma')
('Java', 'May')
('PHP', 'Leon')
('C', 'Bruce')
('JavaScript', 'Anne')
('R', 'Kevin')
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
Advertisement