Machine Learning Tutorials, Courses and Certifications
Related Articles
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.")
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)
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)
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)
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)
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)
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)
