Database Operations: UPDATE and DELETE in MySQL Using Python

Database Operations

Working with databases involves several fundamental operations: Create, Read, Update, and Delete (CRUD). Among these, the UPDATE and DELETE operations are crucial for maintaining and managing data effectively. In this post, we’ll explore how to perform these operations in MySQL using Python, accompanied by real-world examples.

Update Operation

To change the data in one or more table columns, use     the UPDATE-SET command. For precise updates, you must use the WHERE clause to target specific rows; otherwise, all rows in the table will be updated.

SQL Query for Update

The following SQL command updates the name column for the employee with id = 110:

UPDATE Employee SET name = 'alex' WHERE id = 110;

Python Example: Updating a Record

Below is a Python script that demonstrates the update operation using the mysql.connector library.

import mysql.connector  

# Create the connection object  
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)  

# Creating the cursor object  
cur = myconn.cursor()  

try:  
    # Updating the name of the employee whose id is 110  
    cur.execute("UPDATE Employee SET name = 'alex' WHERE id = 110")  
    myconn.commit()  # Save changes  
    print("Record updated successfully.")  
except:  
    myconn.rollback()  # Rollback in case of error  
    print("Error occurred while updating the record.")  

myconn.close()  

Key Points:

• A WHERE clause should always be used to prevent inadvertent modifications.
• Handle exceptions to ensure data integrity.
• Commit changes using myconn.commit() to make them permanent.

Delete Operation

To eliminate particular records from a table, use the DELETE FROM statement. Like the UPDATE operation, a WHERE clause is critical to prevent accidental deletion of all rows.

SQL Query

 for Delete

The following SQL command deletes the employee record with id = 110:

DELETE FROM Employee WHERE id = 110;

Python Example: Deleting a Record

Here’s a Python script demonstrating the delete operation:

import mysql.connector  

# Create the connection object  
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)  

# Creating the cursor object  
cur = myconn.cursor()  

try:  
    # Deleting the employee details whose id is 110  
    cur.execute("DELETE FROM Employee WHERE id = 110")  
    myconn.commit()  # Save changes  
    print("Record deleted successfully.")  
except:  
    myconn.rollback()  # Rollback in case of error  
    print("Error occurred while deleting the record.")  

myconn.close()  

Key Points:

• The WHERE clause can be used to target particular rows.
• Commit changes to ensure the deletion is finalized.
• Always validate inputs to avoid unintentional deletions.

Best Practices for UPDATE and DELETE

1. Backup Data: Always take a backup of critical data before performing updates or deletions.
2. Test Queries: Run queries in a test environment to confirm the desired outcome.
3. Use Transactions: Employ transactions (myconn.commit() and myconn.rollback()) to manage operations safely.
4. Sanitize Inputs: Protect against SQL injection by using parameterized queries.

PHP PROJECT:- CLICK HERE

database operations sql
database operations in mysql
database operations examples
types of database operations
database operations in dbms
database operations in python
database operations in sap abap
database operations crud
database
crud
database operations in sql
database operations pdf