Home » Python » Python programs

Python program to update records in the database

Here, we are going to learn about how to update records in the database table in in Python?
Submitted by Shivang Yadav, on February 21, 2021

We will use Python's pymysql library to work with the database. This library provides the programmer the functionality to run MySQL query using Python.

Algorithm:

• Step 1: Connect to database using connect() method in pymysql.
• Step 2: Get input of faculty ID from the user.
• Step 3: Write a query to fetch the details of the faculty and display it to the user.
• Step 4: Get user input for the tuple (filed) to be changed.
• Step 5: Get user input on the updated values and then update it.

Python program to update records in the database

import pymysql as mysql

try:
    conn=mysql.connect(host='localhost',port=3306,user='root',password='123',db='myschool')
    cmd=conn.cursor()

    id=input("Enter Faculty Id U Want To Update:")
    
    q="select * from faculties where fid='{}'".format(id)
    cmd.execute(q)

    row=cmd.fetchone()
    
    if(row==None):
        print("Not Found")
    else:
        print("ID:",row[0])
        print("1]Name:", row[1])
        print("2]Birth Date:", row[2])
        print("3]Department:", row[3])
        print("4]Salary:", row[4])
        print("5]Exit")
    
        ch=input("Which Field U Want to Edit?")
    
        pat=""
        if(ch=="1"):
            nn=input("Enter New Name:")
            pat="fname='{}'".format(nn)
        elif(ch=="2"):
            nd=input("Enter New DOB:")
            pat="dob='{}'".format(nd)
        elif (ch == "3"):
            nd = input("Enter New Department:")
            pat = "department='{}'".format(nd)
        elif (ch == "4"):
            ns = input("Enter New Salary:")
            pat = "salary={}".format(ns)
        elif(ch=='5'):
            print("Exit")
        else:
            print("Invalid Option")
        if(not pat==''):
            q="update faculties set {} where fid={}".format(pat,id)
            print(q)
            cmd.execute(q)
            conn.commit()
            print("Record Updated")
        conn.close()
except Exception as e:
    print("Error:",e)

Output:

Enter Faculty Id U Want To Update:43
ID:43
1] Name:John
2] BirthDate: 2/4/1985 
3] Department: Science
4] Salary: 45000
5] Exit
Which field U Want to Edit? 4
Enter new Salary: 50000
Record Updated 

Python database (SQL/MySQL) programs »

---