Python Tutorials · Python MySQL

MySQL Update

Learn all about MySQL Update in this comprehensive tutorial.

5 min read advanced

MySQL Drop Table

MySQL Limit

•You can update existing records in a table by using the "UPDATE" statement:
•It is considered a good practice to escape the values of any query, also in update statements.

Update Table

You can update existing records in a table by using the "UPDATE" statement:

python

import mysql.connectormydb = mysql.connector.connect(  host="localhost", 
  user="yourusername",  password="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")

Note: Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.

Note: Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Prevent SQL Injection

It is considered a good practice to escape the values of any query, also in update statements.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

The mysql.connector module uses the placeholder %s to escape values in the update statement:

python

import mysql.connectormydb = mysql.connector.connect(  host="localhost", 
  user="yourusername",  password="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")

Module quiz

2 questions

Which of the following is true about MySQL Update?

It is strictly typedIt is evaluated at runtimeIt requires explicit memory managementIt is deprecated

What is the most common pitfall when working with MySQL Update?

SyntaxErrorIndentationErrorNameErrorTypeError

Answer all questions to submit.