How to Join Multiple MySQL Tables in Python

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python.

Presenting the data

let’s take two (2) tables as a demonstration for the code below.

Users – Table 1

{ id: 1, name: 'Carl', fav: 254},
{ id: 2, name: 'Emma', fav: 254},
{ id: 3, name: 'John', fav: 255},
{ id: 4, name: 'Hayley', fav:},
{ id: 5, name: 'Andrew', fav:}

Products – Table 2

1
2
3

{ id: 254, name: 'Chocolate Chip Cookie Dough' },
{ id: 255, name: 'Buttered Pecan' },
{ id: 256, name: 'Cookies & Cream' }

These two tables can be combined by using users’ fav field and products’ id field.

How to Join Multiple MySQL Tables together in Python

import mysql.connector

mydb = mysql.connector.connect(
  host = "localhost",
  user = "username",
  password = "YoUrPaSsWoRd",
  database = "your_database"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

In the example above, Hayley, and Andrew were excluded from the result, that is because INNER JOIN only shows the records where there is a match.

What is a Left Join in MySQL

If you want to show all users, even if they do not have a favorite product, use the LEFT JOIN statement:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

What is a Right Join in MySQL

If you want to return all products, and the users who have them as their favorite, even if no user has them as their favorite, use the RIGHT JOIN statement:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"