Python MySQL UPDATE Query: Step-by-Step Guide in Python

Python MySQL UPDATE Query: Step-by-Step Guide in Python

This article is about the Python MySQL UPDATE Query to modify the records in your database. If you have a background in SQL, this tutorial will improve your knowledge in handling your database in Python.

Prerequisite

First things first – You need to set up your project. If in case you do not know how to do it, click the link to go to our step by step guide.

Also, if you have any trouble in using the PIP command, just click the link to go to our easy step by step fix. Make sure you have a database to work on.

Sample Code

Let us take a look at the example code. Just like the previous articles, the database used here is “python_db” with a “students” table.

import mysql.connector
import mysql.connector.errors

try:
    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")
    dbCursor = dbConnection.cursor()

    print("Before updating a record ")
    selectQuery = "SELECT * FROM students WHERE studentID = 20200001"
    dbCursor.execute(selectQuery)
    record = dbCursor.fetchone()
    print(record)

    updateQuery= "UPDATE students SET lName = 'Dela Cruz' WHERE studentID = 20200001"
    dbCursor.execute(updateQuery)
    dbConnection.commit()
    print("Record Updated successfully ")

    print("After updating record ")
    dbCursor.execute(selectQuery)
    record = dbCursor.fetchone()
    print(record)

except mysql.connector.Error as error:
    print("Failed to update table record: {}".format(error))

finally:
    if (dbConnection.is_connected()):
        dbConnection.close()
        print("MySQL connection is closed")

The Output

You will get an output similar to this.

Before updating a record
(20200001, 'Juan', 'Ponce', 'Enrile')
Record Updated successfully
After updating record
(20200001, 'Juan', 'Ponce', 'Dela Cruz')
MySQL connection is closed

Parameterized Variation

Here is a parameterized version of the update query. Here, we created a function that needs the ID and the last name of the record. The ID will locate the record while the last name will be the new data. In the query, we substitute these values with placeholders “%s”.

import mysql.connector
import mysql.connector.errors

def updateRecord(ln, id):
    try:
        dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")
        dbCursor = dbConnection.cursor()

        # Update single record now
        updateQuery = "UPDATE students SET lName = %s WHERE studentID = %s"
        updateData = (ln, id)
        dbCursor.execute(updateQuery, updateData)
        dbConnection.commit()
        print("Record Updated successfully ")

    except mysql.connector.Error as error:
        print("Failed to update table record: {}".format(error))

    finally:
        if (dbConnection.is_connected()):
            dbConnection.close()
            print("MySQL connection is closed")

updateRecord( 'Dalisay',20200001)

Updating Multiple Records

Like the the INSERT query, you can update multiple records using the cursor.executemany() method.

import mysql.connector
import mysql.connector.errors

try:
    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")
    dbCursor = dbConnection.cursor()

    updateQuery= "UPDATE students SET lName = %s WHERE studentID = %s"
    batchStudentUpdate = [('Enrile', 20200001),
                          ('Cuachon', 20200002),
                          ('Estrella', 20200003)]
    dbCursor.executemany(updateQuery, batchStudentUpdate)
    dbConnection.commit()
    print("Record Updated successfully ")

except mysql.connector.Error as error:
    print("Failed to update table record: {}".format(error))

finally:
    if (dbConnection.is_connected()):
        dbConnection.close()
        print("MySQL connection is closed")

Conclusion

That’s how you use Python MySQL UPDATE query in your projects. You can always expand and try different ways in implementing the UPDATE statement in your Python projects. Check out the different Python-related projects below:

• Python MySQL SELECT Tutorial
• Hospital Management System in Python
• Student Management System in Python
• Random Password Generator in Python
• Inventory Management System in Python
• Best Python Projects for Beginners
• E-learning Portal System

Inquiries

If you have any questions or suggestions about the python mysql update query, please feel free to leave a comment below.