Skip to content

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:}
Code language: JSON / JSON with Comments (json)

Products – Table 2

{ id: 254, name: 'Chocolate Chip Cookie Dough' }, { id: 255, name: 'Buttered Pecan' }, { id: 256, name: 'Cookies & Cream' }
Code language: JSON / JSON with Comments (json)

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)
Code language: Python (python)

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"
Code language: Python (python)

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"
Code language: Python (python)

See also  Custom RGB To Hex Conversion with Python
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x