UPDATE Data in MySQL Database table using Python

This Python tutorial will focus on how to UPDATE MySQL Database table in Python. We will use the MySQL update query to update records in our database table. In Python, there are several connectors which can establish a connection in between MySQL database and Python program. Using any of those connectors or modules we can easily use all the MySQL queries in our Python Program.

Please note that: Here in this Python MySQL tutorial, we will use MySQL Connector/Python. You can use other modules if you wish, but almost every module is having the same syntax and method for accessing the database.

If you are not familiar with MySQL Connector/Python then these guidelines might help you:

• How to install MySQL Connector in Python
• Connect MySQL with Python
• How to create a database in MySQL using Python
• Create MySQL table in Python
• How to insert data into MySQL Table in Python Programming

If you are done with the above things or already having a MySQL table in your MySQL database with some data then you are ready to update data in your MySQL database table with Python Programming.

Python program to update data in MySQL database table

To show you how to update data in a MySQL table we need a demo table first.

For example here is a table:

demo table to show how to update MySQL database with Python Programming

You can see in the above picture that there is a table having three columns:

category duration level

There are three rows in that table. The table name is codespeedy

Now, we are going to update the duration of the row no 3 from 100 Hours to 150 Hours.

The row no 3 is:

('Python', '100 Hours', 'intermediate')

Here category is Python and the duration is 100 Hours. We have to modify the duration of this row.

Here is the Python program to modify data in MySQL Table

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_username_here",
  passwd="put_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_statement = "UPDATE CODESPEEDY SET duration='150 Hours' where category='Python'"

my_database.execute(sql_statement)
db_connection.commit()

Output with no errors indicates that your program was successful.

Though you can check if your data is updated or not with Fetch data from MySQL table in Python Program

After updating the data the table will look like this:

Updated data in MySQL table

SELECT * FROM codespeedy;

('Java', '80 Hours', 'Advanced')
('Php', '70 Hours', 'Basic')
('Python', '150 Hours', 'intermediate')

MySQL statement to update a table data is:

UPDATE CODESPEEDY SET duration='150 Hours' where category='Python'

If you don’t use the where clause that means you are not targetting a particular row to be updated. In this case, all the rows will be updated. So be careful while using the UPDATE query.

Note that: Use of commit() in your program means that you are committing the changes you made through the MySQL statement execution. If you forget to use the commit() no changes will be saved.

Steps we followed to update MySQL Table in Python program:

1. Imported mysql.connector.
2. Created the database connection.
3. Store MySQL update query in a variable.
4. Used execute() method to execute the UPDATE Query.
5. Finally, we committed our changes by using the commit() method.

This was a basic tutorial on data updating in MySQL with Python. But this is not considered a good practice.
So we strongly suggest you escape values of any MySQL query to prevent SQL Injection.

Update MySQL Data Securely in Python – SQL Injection prevention

Here again, we are taking the previous demo table as an example table.

We are also going to update the same row.

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_username_here",
  passwd="put_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_statement = "UPDATE CODESPEEDY SET duration= %s where category= %s"
data = ("150 Hours", "Python")

my_database.execute(sql_statement,data)
db_connection.commit()

It will also work as we are done before. But the difference is it is a more secure way of updating data.

SQL injection is mainly a hacking technique for hackers. By using this technique hackers can delete your data or misuse your data.

Another positive point:

You can take user input with the command line as we are storing the values in a variable.

Update multiple rows at a single time in MySQL Python

To update multiple rows at once you can simply use this MySQL Statement:

UPDATE CODESPEEDY SET duration='150 Hours' where category='Python' or category='Java'"

Our demo table:

demo table to show how to update multiple rows with Python Programming

Update multiple rows in MySQL using Python

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_username_here",
  passwd="put_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_statement = "UPDATE CODESPEEDY SET duration='150 Hours' where category='Python' or category='Java'"

my_database.execute(sql_statement)
db_connection.commit()

The above Python program will update multiple rows in a single query.

Output:

Table – after the execution of the above program to update multiple rows in MySQL