First, you have to the mysql.connector. In case you are not sure of the right way to get this setup, seek advice from The right way to Set up MySQL Driver in Python.
Presenting the information
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 might be mixed through the use of customers’ fav area and merchandise’ id area.
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 had been excluded from the consequence, that’s as a result of INNER JOIN solely exhibits the data 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"
