How to Get All Rows From table2 in Python MySQL


In this example, we will learn how to get all rows from the table2, even if there are no matches in the table1.

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 
       RIGHT 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')
(None, 'Amy')
(None, 'Kate')
(None, 'Ben')
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
Advertisement