The right way to Be a part of A number of MySQL Tables in Python

First, you will have the mysql.connector. If you’re uncertain of tips on how to get this setup, check with The right way to Set up MySQL Driver in Python.

Presenting the info#

let’s take two (2) tables as an illustration for the code beneath.

Customers – Desk 1

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

Merchandise – Desk 2

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

These two tables may be mixed by utilizing customers’ fav discipline and merchandise’ id discipline.

The right way to Be a part of A number of MySQL Tables collectively in Python#

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT 
  customers.identify AS consumer, 
  merchandise.identify AS favourite 
  FROM customers 
  INNER JOIN merchandise ON customers.fav = merchandise.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Within the instance above, Hayley, and Andrew have been excluded from the end result, that’s as a result of INNER JOIN solely reveals the information the place there’s a match.

What’s a Left Take part MySQL#

If you wish to present all customers, even when they don’t have a favourite product, use the LEFT JOIN assertion:

sql = "SELECT 
  customers.identify AS consumer, 
  merchandise.identify AS favourite 
  FROM customers 
  LEFT JOIN merchandise ON customers.fav = merchandise.id"

What’s a Proper Take part MySQL#

If you wish to return all merchandise, and the customers who’ve them as their favourite, even when no consumer has them as their favourite, use the RIGHT JOIN assertion:

sql = "SELECT 
  customers.identify AS consumer, 
  merchandise.identify AS favourite 
  FROM customers 
  RIGHT JOIN merchandise ON customers.fav = merchandise.id"