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.

Technology stack and requirements

To run this Python project on your development machine, you need:

• Python 3.10 or higher. Download from python.org or install via Anaconda if you prefer bundled packages.
• pip package manager. Comes with Python. Used to install project dependencies from requirements.txt.
• Virtual environment. Use venv or conda to isolate project dependencies from your global Python install.
• VS Code or PyCharm. Free code editors with Python syntax highlighting, IntelliSense, and debugging.
• Git. For version control and cloning source code repositories.

Installing the source code

1. Download or clone the repository. Get the ZIP archive from the download link on this page and extract it.
2. Create a virtual environment. Open a terminal in the project folder and run: python -m venv venv, then activate it (venv\Scripts\activate on Windows or source venv/bin/activate on Mac/Linux).
3. Install dependencies. Run pip install -r requirements.txt to install all libraries the project needs.
4. Configure environment variables. If the project uses API keys (OpenAI, Anthropic, database), create a .env file and set the required keys.
5. Run the project. Follow the run command in the README (usually python main.py or streamlit run app.py).

Using this project for your BSIT capstone

• Chapter 1 (Introduction). Discuss the real-world problem this system solves. Cite Philippine or international use cases where the manual process could be automated.
• Chapter 2 (RRL). Compare your project against 5-10 similar published works. Cite ACM, IEEE, or arXiv papers for academic-standard sources.
• Chapter 3 (Methodology). Document the model architecture, training data, hyperparameters, and evaluation metrics used.
• Chapter 4 (Results). Report accuracy, precision, recall, F1-score, and confusion matrix. Screenshot the running app on real inputs.
• Chapter 5 (Conclusion). Identify features for Version 2: better model, larger dataset, mobile deployment, or REST API.

Modules typical of Python MySQL UPDATE Query: Step-by-Step Guide

• Core Python logic. Main functions implementing the business logic of the system.
• Data storage. SQLite for simple projects, PostgreSQL or MongoDB for larger data.
• User interface. Tkinter for desktop, Streamlit for data dashboards, or Flask/FastAPI for web.
• Input validation. Type checking and range validation before processing user data.
• Reports. CSV or PDF export using pandas.to_csv() or ReportLab.
• Testing. pytest unit tests covering core functions.

Common enhancements for capstone review

• Add REST API. Convert desktop app to FastAPI service for mobile or web front-ends.
• Multi-user support. Add login, roles, and per-user data isolation.
• Cloud deployment. Deploy to Render, Railway, or Fly.io for public access.
• Docker containerization. Package the app in Docker for portable deployment.