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

First, you’ll need the mysql.connector. If you’re uncertain of how one can get this setup, consult with The way to Set up MySQL Driver in Python.

Presenting the info

let’s take two (2) tables as an indication for the code under.

Customers – Desk 1

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

Merchandise – Desk 2

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

These two tables might be mixed through the use of customers’ fav area and merchandise’ id area.

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

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT 
  customers.title AS person, 
  merchandise.title 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 had been excluded from the outcome, 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.title AS person, 
  merchandise.title 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 person has them as their favourite, use the RIGHT JOIN assertion:

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