How to Update MySQL Table Python

In Python, the UPDATE operation is frequently utilized to modify current records within a database. To update the information of a specific row, it is essential to incorporate the WHERE clause alongside the identifier of that particular row.

MySQL Update Table Syntax

The UPDATE operation has the following syntax:

UPDATE table_name

SET col1 = val1, col2 = val2...., colN = valN

WHERE [your_condition];

Working on the Database

Database name : mydatabase

Table : customers

The table has the following values.

Python MySQL Update Operation Example

Let’s consider an illustration to showcase the process of modifying an existing table.

Example

import mysql.connector

# Connecting to MySQL server

db_connection = mysql.connector.connect(

    host="localhost",

    user="root",

    password="Root",

    database="mydatabase"

)

# Create a cursor object

db_cursor = db_connection.cursor()

# ID of the record to be updated

record_id = 8

# Printing the record before performing the update

print("Before Updating the record:")

db_cursor.execute("SELECT * FROM customers WHERE id = %s", (record_id,))

before_update = db_cursor.fetchone()

print(before_update)

# Witing the query to perform the UPDATE operation

update_query = "UPDATE customers SET name = %s, address = %s WHERE id = %s"

new_data = ("Reema", "Kolkata", record_id)

# Execute update

db_cursor.execute(update_query, new_data)

db_connection.commit()

# Print record after update

print("\nAfter Updating the record:")

db_cursor.execute("SELECT * FROM customers WHERE id = %s", (record_id,))

after_update = db_cursor.fetchone()

print(after_update)

# Closing the connection using close() method

db_cursor.close()

db_connection.close()

Output:

Before Updating the record:

('Divya', 'Kolkata', 8)

After Updating the record:

('Reema', 'Kolkata', 8)

Explanation:

In the program presented above, we have established a connection between Python and the MySQL database by utilizing the mysql.connector library.

The connection is initiated by utilizing the specifics of the host (localhost), username (root), password (Root), and the database name (mydatabase).

We have instantiated a cursor object referred to as db_cursor, which is utilized for executing SQL statements.

We have crafted a SQL statement intended to UPDATE the database, utilizing placeholders (%s). In this process, we have substituted the previous values with the updated values corresponding to the identifier number 8.

In order to ensure that the modifications are saved permanently, we have utilized the commit function.

Prevent SQL Injection

In SQL, it is considered best practice to escape the values in any query, which includes UPDATE statements. SQL injection attacks represent a prevalent method employed by hackers to manipulate and exploit your database. To mitigate the risk of SQL Injection, the %s placeholder method is utilized.

The mysql.connector library employs the placeholder %s to safely escape values within the update statement:

Example

import mysql.connector

# Connecting to MySQL server

db_connection = mysql.connector.connect(

    host="localhost",

    user="root",

    password="Root",

    database="mydatabase"

)

# Create a cursor object

db_cursor = db_connection.cursor()

# Writing a SQL UPDATE to prevent SQL injection

sql = "UPDATE customers SET address = %s WHERE address = %s"

val = ("Goa", "Kolkata")  # changing the value'Kolkata' value to 'Goa'

db_cursor.execute(sql, val)

# using commit() method to save the changesn

db_connection.commit()

# Printing the number of rows that we have updated

print(db_cursor.rowcount, "record(s) affected")

# Closing the connection

db_cursor.close()

db_connection.close()

Output:

Explanation:

In the program presented above, we established a connection between Python and the MySQL database by utilizing the mysql.connector library.

The connection is initiated utilizing the information pertaining to the host (localhost), username (root), password (Root), and the database (mydatabase). A cursor object, referred to as db_cursor, has been created to facilitate the execution of SQL statements.

We have constructed a SQL UPDATE statement employing the %s placeholder technique to safeguard against SQL injection vulnerabilities. In this query, we have substituted the previous value 'Kolkata' with the updated value 'Goa'.

In order to make the modifications permanent, the commit method has been utilized, and db_cursor.rowcount has been employed to display the total count of rows that have been modified.

In conclusion, we have successfully terminated the cursor and the database connection by utilizing the close method.

Conclusion

In SQL, the UPDATE statement serves the purpose of altering or adjusting the current records within your database. This functionality is highly valuable, as it enables the correction of any erroneous entries that may have been made in the database. By utilizing the UPDATE statement in conjunction with a WHERE clause, you can target and modify particular rows while leaving the remaining data in the table unchanged.

Moreover, leveraging placeholders such as %s enables Python programmers to safeguard against SQL injection attacks. Implementing the UPDATE statement not only ensures that records are current but also plays a crucial role in preserving the integrity of the data within your application.

Frequently Asked Questions/FAQs

1. What is SQL Injection? How can you prevent it?

SQL Injection represents a prevalent method of web exploitation employed by cybercriminals to alter a SQL query and exploit the database. This vulnerability can be effectively mitigated by incorporating placeholders (%s) within SQL statements.

2. What should be the syntax to check how many rows were affected by the UPDATE query?

• ✅ db_cursor.count
• ✅ dbcursor.affectedrows
• ✅ db_cursor.rowcount
• ✅ db_cursor.rows

c. db_cursor.rowcount

3. What is the purpose of using the commit method in a SQL database?

The db_connection.commit function is essential for persisting modifications made to your database. Failing to invoke this method will result in no alterations being applied to the table.

4. What happens if we don't use the WHERE clause while using the UPDATE statement in a SQL database?

The WHERE clause serves the purpose of defining the specific records you wish to modify within the database. Omitting the WHERE clause will result in the modification of every record present in the database.