Python update table

Update table

You can use the 'UPDATE' statement to update existing records in the table:

Example

Replace 'Valley 345' in the address column with 'Canyon 123':

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

Run Instance

Important:Please note the statement mydb.commit()Changes need to be made, otherwise there will be no change to the table.

Please note the WHERE clause in the UPDATE syntax: The WHERE clause specifies the records to be updated. If the WHERE clause is omitted, all records will be updated!

Prevent SQL Injection

It is a good habit to escape any queried value in the update statement.

This is to prevent SQL injection, a common network hacker technique that can damage or misuse your database.

The mysql.connector module uses placeholders ％s To escape values in the delete statement:

Example

Use the placeholder %s method to escape values:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE address =" %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

Run Instance