Python MySQL Update Records

Change existing data - edit and modify your database records!

✏️ Update Your Data!

UPDATE changes existing records - like using an eraser and writing new information! 


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()
sql = "UPDATE students SET age = %s WHERE name = %s"
cursor.execute(sql, (17, "John Smith"))
mydb.commit()

print(f"✅ {cursor.rowcount} record updated")
UPDATE
Command
SET
New Values
WHERE
Condition

What is UPDATE?

UPDATE changes existing records:

  • ✏️ Edit Records - Change existing data in your table
  • 🎯 Specific Changes - Update only what matches your condition
  • 🔄 Keep Original - Only changes what you specify
  • ⚠️ Use Carefully - Always use WHERE to avoid changing everything

Update One Record

Update Student's Age 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Update one student's age
sql = "UPDATE students SET age = %s WHERE name = %s"
new_age = 17
student_name = "Alice Johnson"

cursor.execute(sql, (new_age, student_name))
mydb.commit()

print(f"✅ Updated {student_name}'s age to {new_age}")
print(f"📊 {cursor.rowcount} record changed")

mydb.close()

🔑 UPDATE Parts

  • UPDATE table_name - Which table to change
  • SET column = value - What to change
  • WHERE condition - Which records to update

Update Multiple Things

Update Age and Grade Together 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Update multiple columns at once
sql = "UPDATE students SET age = %s, grade = %s WHERE name = %s"
new_age = 16
new_grade = "11th"
student_name = "Bob Smith"

cursor.execute(sql, (new_age, new_grade, student_name))
mydb.commit()

print(f"✅ Updated {student_name}: age = {new_age}, grade = {new_grade}")

mydb.close()

Update Multiple Records

Update All Students in a Grade 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Update all 9th graders to 10th grade (promotion)
cursor.execute("UPDATE students SET grade = %s WHERE grade = %s", ("10th", "9th"))
mydb.commit()

print(f"🎓 Promoted {cursor.rowcount} students from 9th to 10th grade")

mydb.close()

Safe Update Function

Update with Confirmation 

import mysql.connector

def safe_update_student(student_name, new_age):
    """Safely update student with confirmation"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        # First, check if student exists
        cursor.execute("SELECT name, age FROM students WHERE name = %s", (student_name,))
        result = cursor.fetchone()
        
        if result:
            old_age = result[1]
            print(f"📋 Found: {result[0]} (currently {old_age} years old)")
            
            # Update the student's age
            cursor.execute("UPDATE students SET age = %s WHERE name = %s", (new_age, student_name))
            mydb.commit()
            
            print(f"✅ Updated {student_name}'s age from {old_age} to {new_age}")
        else:
            print(f"❌ Student '{student_name}' not found")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# Use the function
safe_update_student("Carol Davis", 18)

Update with Math

Increase All Ages by 1 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Add 1 year to everyone's age (birthday celebration!)
cursor.execute("UPDATE students SET age = age + 1")
mydb.commit()

print(f"🎂 Added 1 year to {cursor.rowcount} students (Happy Birthday!)")

mydb.close()

Preview Updates

See What Will Be Updated 

import mysql.connector

def preview_update():
    """Show what will be updated before actually updating"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        # Show current 10th graders
        cursor.execute("SELECT name, age, grade FROM students WHERE grade = %s", ("10th",))
        students = cursor.fetchall()
        
        if students:
            print(f"📋 These {len(students)} students will be promoted to 11th grade:")
            for student in students:
                print(f"  - {student[0]} ({student[1]} years old)")
                
            # Uncomment to actually update
            # cursor.execute("UPDATE students SET grade = %s WHERE grade = %s", ("11th", "10th"))
            # mydb.commit()
            # print(f"✅ Updated {cursor.rowcount} students")
        else:
            print("❌ No 10th grade students found")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# Preview the update
preview_update()

UPDATE Safety Tips

⚠️ Always Use WHERE

Without WHERE, you update ALL records!

🔍 Check First

Use SELECT to see what you'll update

🔒 Use Placeholders

Always use %s, never string formatting

💾 Commit to Save

Don't forget commit() to make it permanent

🧠 Test Your Knowledge

Which part of UPDATE sets the new value?

What happens if you forget WHERE in UPDATE?