Python-MySQL Update Records

In this tutorial, we will learn to update the MySQL table records using the python program. It is very easy to modify the records using a python program. There are two ways you can edit the table records- using a simple query as well as a prepared query. In this tutorial, we will see the demonstration of both methods. Let’s dive in!

Also read: Python MySQL – Connect a MySQL Database with Python

Before We Start

Before we demonstrate the examples, you must know how the UPDATE query works in MySQL. Let’s revise some terms really quick-

To update particular a value/s of all records –

UPDATE table_name SET col_name=value;Code language: SQL (Structured Query Language) (sql)

The above query will set the given value to that column of all rows.

To update only a single row / particular set of rows –

UPDATE table_name SET col_name=value 
WHERE condition;Code language: SQL (Structured Query Language) (sql)

In the above query, you may set the condition with the WHERE clause such as “WHERE id = 1”, “WHERE id>10 AND id<20”, “WHERE id BETWEEN 10 AND 20” and similar to these. Note that, you must set a valid condition to capture the target row.

Python-MySQL Update Records

To demonstrate the update operation, we are using the “emp” table having the following description and the data.

Update Record using Simple Query

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "UPDATE emp SET name='Johny' WHERE id=2"
mycursor.execute(query)
mydb.commit()
print(mycursor.rowcount, " record(s) affected")
mydb.close()Code language: Python (python)

In the above code, we first created a cursor object.

Then we write a simple update query to update the name of the record having id 2.

Then we execute the query using execute() method.

Note that, unless you write a commit() method, the update operation won’t take place in the MySQL database table. It is a necessary and the most important statement to modify the record(s).

Let’s run the program and check the emp table to see if the changes take place.

As you can see, the name of the record of id 2 is changed from john to Johny.

Update Multiple Records using Simple Query

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "UPDATE emp SET city ='New York' WHERE id>=5 AND id<=8"
mycursor.execute(query)
mydb.commit()
print(mycursor.rowcount, " record(s) affected")
mydb.close()Code language: Python (python)

Here, we are using the comparison operators (<=, >=) to select the range of the records.

Using the above update query, we are targeting the rows having id 5,6,7 and 8.

When we execute the above python program, we must get the output – 4 records affected.

Let’s run the program and see what we get on the terminal and also check the MySQL table to see if the changes are made.

As you can see, four records are modified.

Update Records by Preventing SQL Injection

It is very important that only valid and proper values must be inserted into the table. However, hackers might try to access or destroy your database using common hacking techniques such as SQL injections.

The mysql.connector module offers you to use %s placeholder to escape the values in the update query.

Now let’s see it in a practical way.

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "UPDATE emp SET city=%s WHERE id=%s"
values = ("Banglore", 1)
mycursor.execute(query,values)
mydb.commit()
print(mycursor.rowcount, " record(s) affected")
Code language: Python (python)

Here, we first write an update query without mentioning any values, neither new values nor condition values.

Then we create a tuple that will hold the values that should be paired with the recently created update query.

Note that, the tuple must consist of an equal number of values as the %s is specified.

Finally, we execute the query and commit the changes in the database.

Let’s run the program and check the table to see if the changes are made.

As you can see, the record having id 1 is updated.

Conclusion

In this tutorial, we have learned how to update the records using a simple query as well as using a prepared query. It is recommended to use the second method for updating the records when you are getting the inputs from the user. Happy learning!!