Thursday , December 2 2021
Home / Python / Python MySQL Join

Python MySQL Join

12 Python MySQL Join

Python MySQL Join

  • Join Two or More Tables
  • You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
  • Consider you have a “customers” table and a “product” table:

Create Table Name Product

In [29]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE product (id int, prodname VARCHAR(255))")

sql = "INSERT INTO product (id, prodname) VALUES (%s, %s)"
val = [
  ('1', 'iPhone'),
  ('2', 'Charger'),
  ('3', 'Data Cable'),
  ('4', 'Laptop'),
  ('5', 'Pen Drive'),
]

mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "record inserted.")
5 record inserted.

Inner Join is same as Join

In [45]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')

Select Columns as per require

In [36]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Inner Join

In [37]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers INNER JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Left Join

In [38]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers LEFT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')
(6, 'Atharv', 'Goa', None)
(7, 'Sia', 'Dubai', None)

Python MySQL Right Join

In [39]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers RIGHT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(None, None, None, 'Charger')
(None, None, None, 'Data Cable')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL OUTER or FULL Join

  • You don’t have FULL JOINS on MySQL, but you can sure emulate them using Union
In [47]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * from customers as t1 LEFT JOIN product as t2 ON t1.id = t2.id \
 UNION SELECT * from customers as t1 RIGHT JOIN product as t2 ON t1.id = t2.id "

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')
(6, 'Atharv', 'Goa', None, None)
(7, 'Sia', 'Dubai', None, None)
(None, None, None, 2, 'Charger')
(None, None, None, 3, 'Data Cable')

About Machine Learning

Check Also

Python MySQL Insert Into Table

4 Python MySQL Insert Into Table Python MySQL Insert Into Table¶To fill a table in …

Leave a Reply

Your email address will not be published. Required fields are marked *