Database connectivity using Python

In this article we’ll be covering all database examples like SELECT, INSERT, UPDATE and DELETE operations using Python and MySQL database.

First,, create database[college_db] and table[student_info] as per following image and insert few records  [here we using wamp server]  before you execute all programs in Python.

a) SELECT operation

# 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()

db_cursor.execute('SELECT * FROM student_info')
table_rows = db_cursor.fetchall()
#without column heads
#df = pd.DataFrame(table_rows)
#print(df)
#with column heads
ch = pd.read_sql('SELECT * FROM student_info', con=db_connection)
print(ch)

Output of above code is :

b) INSERT record using GUI[tkinter]

# GUI - Event
from tkinter import *

#object on class Tk
window = Tk()
window.title("Welcome to GUI Python")
window.geometry('350x200')

#declaring controls
regno=Label(window,width=20, text="Reg No:", bg="yellow")
sname=Label(window, width=20,text="Name:", bg="yellow")
course=Label(window, width=20,text="Course:", bg="yellow")
ac_yr=Label(window, width=20,text="Academic Year:", bg="yellow")

txt1 = Entry(window,width=20)
txt2 = Entry(window,width=20)
txt3 = Entry(window,width=20)
txt4 = Entry(window,width=20)

#placing controls, using grid
regno.grid(column=0, row=0)
sname.grid(column=0, row=1)
course.grid(column=0, row=2)
ac_yr.grid(column=0, row=3)

txt1.grid(column=1, row=0)
txt2.grid(column=1, row=1)
txt3.grid(column=1, row=2)
txt4.grid(column=1, row=3)

#function to establish db connection and db operations
def clicked():
    import mysql.connector as sql
    import pandas as pd
    db_connection = sql.connect(host='localhost', database='college_db', user='root', password='')
    db_cursor = db_connection.cursor()
    t1=txt1.get()
    t2=txt2.get()
    t3=txt3.get()
    t4=txt4.get()
    
    db_cursor.execute("INSERT INTO student_info(regno,sname, course, aca_yr) VALUES(%s, %s, %s ,%s)", [str(t1),str(t2),str(t3),str(t4)])
    db_connection.commit()
    res="Record added successfully!"
    lb=Label(window, bg="cyan", text=res).grid(column=1,row=6)
      
btn = Button(window, text="Add Record",width=20,bg="blue",fg="white", command=clicked)
btn.grid(column=1, row=4)

#infinite loop to run application,
#wait for an event to occur and process event as long as win is not closed.
window.mainloop() 

Output of above code is :

c) UPDATE record

# 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()

db_cursor.execute("UPDATE student_info SET course='MCA' WHERE regno=1004")

db_connection.commit()
print('Record Updated')

ch = pd.read_sql('SELECT * FROM student_info', con=db_connection)
print(ch)

Output of above code is :

d) DELETE record

# 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()
db_cursor.execute("DELETE from student_info WHERE regno=110")
db_connection.commit()
print('Record Deleted')

ch = pd.read_sql('SELECT * FROM student_info', con=db_connection)
print(ch)

Output of above code is :