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

In this example we will show 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')