Update Record In MySQL Table

In Python, To Update a record in MySQL table using Python, First, we need to install "pip" if haven't installed it already.

Python provides the "mysql-connector-python" library to connect the database with the Python application.

Below is an example demonstrating how to update a record in a table:

Install mysql connector library:

After installing the package, we can use it to establish a connection to our MySQL database and perform operations.

Example:

pip install mysql-connector-python

Import Library and Database configuration parameters:

Import `mysql.connector` library and create a configuration object with keys such as `user`, `password`, `host`, and `database`.

Example:

#import mysql connector library
import mysql.connector

# Configuration parameters
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',  # or your host address
    'database': 'your_database_name',
}

Update Record Query:

Update Record in Table using the `UPDATE` command and with the help execute() method we can run the operation to update records in the MySQL table.

Example:

# Define the update query
update_query = "UPDATE your_table_name SET age = %s WHERE name = %s"

# New data for update
new_age = 23
name_to_update = "Alice"

# Execute the update query
cursor.execute(update_query, (new_age, name_to_update))

# Commit the transaction
connection.commit()

print("Record updated successfully")

Close Connection:

Don't forget to close the connection once done using the "close()" method.

Example:

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Code Snippet for Established MySQL Database Connection:

Example:

import mysql.connector

# Configuration parameters
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',  # or your host address
    'database': 'your_database_name',
}

try:
    # Establish a connection to the MySQL server
    connection = mysql.connector.connect(**config)

    if connection.is_connected():
        # Create a cursor object to execute SQL queries
        cursor = connection.cursor()

        # Define the update query
        update_query = "UPDATE your_table_name SET age = %s WHERE name = %s"

        # New data for update
        new_age = 23
        name_to_update = "Alice"

        # Execute the update query
        cursor.execute(update_query, (new_age, name_to_update))

        # Commit the transaction
        connection.commit()

        print("Record updated successfully")

except mysql.connector.Error as error:
    print("Error occurred while connecting to MySQL:", error)

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Make sure to replace 'your_username', 'your_password', and 'your_database_name' with your actual MySQL credentials and database name.
Replace 'your_table_name' with the name of the table you want to update records in.
This script connects to the MySQL server, updates the age of the record with the name "Alice" in the specified table to "23", and prints a success message if the operation is successful.
Remember to handle exceptions and close the connection properly.

<-- Select Record

Delete Record -->