MYSQL Joins with Python

Inner join example to retrieve records from two table.

Here are those two table:

Now we will retrieve records from both tables based on regno column.

Source code is: 

# pip install mysql-connector
import mysql.connector as sql
# pip install pandas
import pandas as pd

db_connection = sql.connect(host='localhost', database='college_db', user='root', password='')
db_cursor = db_connection.cursor()

ch = pd.read_sql('SELECT student_info.sname,course.title FROM student_info INNER JOIN course ON course.regno=student_info.regno', con=db_connection)
print(ch)

Output of above code is :

In the above example only two records are matching i.e. regno 1002 and 1008 hence inner join, where records are displayed based on matching entries in either side of the tables.

Another example on Inner join to know balance fee of a student.

First, let us design tables as following :

Now we will retrieve records based on regno from both the tables.

Source code:

# pip install mysql-connector
import mysql.connector as sql

# pip install pandas
import pandas as pd 

db_connection = sql.connect(host='localhost', database='college_db', user='root', password='')
db_cursor = db_connection.cursor()

ch1 = pd.read_sql("SELECT * from fee_master where regno='1001' ", con=db_connection)
print("Fee Master Table ")
print("________________________________")
print(ch1)

ch2 = pd.read_sql("SELECT * from fee_det where regno='1001' ", con=db_connection)
print("Fee Detail Table")
print("________________________________")
print(ch2)

print("Inner Join to coorelate data")
print("________________________________")
ch3= pd.read_sql("select  fee_master.totfee-sum(fee_det.feeamt) as 'Balance fee' from fee_master INNER JOIN fee_det ON fee_master.regno = fee_det.regno where fee_master.regno='1001' GROUP BY fee_det.regno", con=db_connection)
print(ch3)

Output of the above example is: