Python MySQL Update

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

by

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.

How to use Python MySQL UPDATE query

  1. Import mysql.connector

    This will connect your project/program to Python’s MySQL module so that your work can communicate to your database.

  2. Use the connect() method

    Using mysql.connector.connect() will establish a connection to your database. You can set the host, user, password, and database name as parameters of this method.

  3. Preparing the SQL

    Next is to prepare your query to update the records from your table. It is important here to know the fields in your table. Create an appropriately named variable to store your SQL. We will use this later as a parameter.

  4. The cursor() method

    This will create a cursor object that will interact with the database.

  5. Execute the UPDATE query with execute()

    Use your SQL variable as parameter for the execute method.

  6. Closing the connection

    Lastly, close the connection of your database.

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:

Inquiries

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

Leave a Comment