PYnative

Python Programming

Home » Python » Databases » Python Update MySQL Table

Python Update MySQL Table

Updated on:  | 14 Comments

This article demonstrates how to execute a MySQL UPDATE query from Python to modify the MySQL table’s data.

Goals of this lesson. You’ll learn the following MySQL UPDATE operations from Python using a ‘MySQL Connector’ module.

  • Update single and multiple rows, single and multiple columns
  • Use a Python variable in a parameterized query to update table rows.
  • Also, Update a column with date-time and timestamp values
  • The role of commit and rollback in the update operation.

Further Reading:

Table of contents

Prerequisite

Before executing the following program, make sure you have the following in place −

  • Username and password that you need to connect MySQL
  • MySQL database table name which you want to update.

For this article, I am using a Laptop table present in my MySQL server.

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your UPDATE operations.

MySQL Laptop table with data
MySQL Laptop table with data

Example to Update a row of MySQL Table

To perform a SQL UPDATE query from Python, you need to follow the following steps: –

How to Update MySQL Table in Python

  1. Connect to MySQL from Python

    Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module

  2. Prepare a SQL Update Query

    Prepare an update statement query with data to update. FOr example, UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

  3. Execute the UPDATE query, using cursor.execute()

    Execute the UPDATE query using cursor.execute() method. This method execute the operation stored in the UPDATE query.

  4. Commit your changes

    Make modification persistent into a database using the commit() of a connection class.

  5. Extract the number of rows affected

    After a successful update operation, use a cursor.rowcount method to get the number of rows affected. The count depends on how many rows you are updating.

  6. Verify result using the SQL SELECT query

    Execute a MySQL select query from Python to see the new changes

  7. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Python update MySQL table's data
Python update MySQL table’s data

Let’s see the program now. In this program, we are updating a Laptop table by changing the price column of the first row.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()

    print("Before updating a record ")
    sql_select_query = """select * from Laptop where id = 1"""
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)

    # Update single record now
    sql_update_query = """Update Laptop set Price = 7000 where id = 1"""
    cursor.execute(sql_update_query)
    connection.commit()
    print("Record Updated successfully ")

    print("After updating record ")
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)

except mysql.connector.Error as error:
    print("Failed to update table record: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")
Code language: Python (python)

Output:

Before updating a row
(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))
Record Updated successfully 

After updating row
(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))
MySQL connection is closed
MySQL Laptop table after updating record
MySQL Laptop table after updating record

Note:

  • Don’t forget to close the cursor and database connection objects and Catch any SQL exceptions that may occur during this process
  • We also used commit() and rollback() method. commit() to make changes persistent in the database and rollback() revert the changes if any database error occurred.

Use a Python variable in MySQL Update query

Sometimes we need input from the user, such as when users update their password or any other details through User Interface. Or when you want to update details dynamically by passing Python variables into a query. Such as setting column value using the variable.

It is always best practice to use parameterized query and prepared statement, i.e., placeholders ( %s ) inside any SQL statements that contain input from users. This helps us prevent SQL injection and other SQL issues.
Read more on What is a Parameterized Query and its performance benefits.

Let’ s see the example program now.

import mysql.connector

def update_laptop_price(id, price):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='electronics',
                                             user='roo',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_update_query = """Update laptop set price = %s where id = %s"""
        input_data = (price, id)
        cursor.execute(sql_update_query, input_data)
        connection.commit()
        print("Record Updated successfully ")

    except mysql.connector.Error as error:
        print("Failed to update record to database: {}".format(error))
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

update_laptop_price(7500, 1)
update_laptop_price(5000, 2)Code language: Python (python)

Output: –

Record Updated successfully 
MySQL connection is closed
Record Updated successfully 
MySQL connection is closed

Let’s understand the above program: –

  • We used the prepared statement to accept user input using a placeholder, i.e., we put two placeholders in the update query, one for the “Price” column and the other is for the” id” column.
  • Next, we added those two columns value in the tuple format in sequential order and passed SQL update query and input tuple to the cursor.execute() method. Remember tuple contains user data in the sequential order of placeholders.
  • In the end, we are committing our changes to the database using the connection.commit().

Update Multiple Rows of MySQL Table using Python

It is possible to update multiple rows in a single SQL Query. You can also call it a bulk update. Use the cursor.executemany() method of cursor object to update multiple rows of a table.

The syntax of the executemany() method

cursor.executemany(operation, seq_of_params)Code language: Python (python)

This method executes operation against all parameter sequences in the sequence seq_of_params argument.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Price = %s where id = %s"""

    # multiple records to be updated in tuple format
    records_to_update = [(3000, 3), (2750, 4)]
    cursor.executemany(sql_update_query, records_to_update)
    connection.commit()

    print(cursor.rowcount, "Records of a laptop table updated successfully")

except mysql.connector.Error as error:
    print("Failed to update records to database: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")
Code language: Python (python)

Output:

2 Records of a laptop table updated successfully
connection is closed

Let’s  understand the above program

  • We defined a SQL update query with two placeholders (“Price” and “Id” column ). and prepared a list of records to be updated. This List contains a tuple for each row. Here we created two tuples, so we are updated two rows.
  • We used the cursor.executemany() method to update multiple rows of a database table.
  • Using the cursor.rowcount we can find how many rows are updated successfully.

Python update multiple Columns of MySQL table

We can also update multiple columns of the MySQL table in a single query. Use parameterized query using a placeholder to update multiple columns. Let see this with an example program.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Name = %s, Price = %s where id = %s"""

    name = "HP Pavilion"
    price = 2200
    id = 4
    input = (name, price, id)

    cursor.execute(sql_update_query, input)
    connection.commit()
    print("Multiple columns updated successfully ")

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

finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")
Code language: Python (python)

Output:

Multiple column updated successfully
MySQL connection is closed

Update Datetime and timestamp column of a MySQL table from Python

Suppose you have a date column in a MySQL table and you want to update a datetime.datetime() object into this column. Let’s see how to prepare an update query to update the datetime column of a table

from datetime import datetime

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Purchase_date = %s where id = %s"""

    current_Date = datetime.now()
    formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
    id = 2
    input = (formatted_date, id)
    cursor.execute(sql_update_query, input)
    connection.commit()
    print("Purchased Date Updated successfully ")

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

finally:
    if connection.is_connected():
        connection.close()
        print("connection is closed")
Code language: Python (python)

You should get the following output after the execution of the above code.

Purchased Date Updated successfully 
connection is closed

Next Steps

To practice what you learned in this lesson, Please solve a Python Database Exercise project to practice and master the Python Database operations.

TweetF  sharein  shareP  Pin

About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

Related Tutorial Topics:

Python Python Databases

All Coding Exercises:

C Exercises
C++ Exercises
Python Exercises

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 25+ questions
  • Each Quiz contains 25 MCQ
Exercises
Quizzes

Comments

  2. reda ali says 

    def update_student (self) :

        con = pymysql.connect (host=  'localhost',user = 'root',password = '',database = 'stud')
        cur = con.cursor ()
        cur.execute("update student set (mu=%s,add=%s,phone=%s,jn=%s,name=%s where num=%s)"
                                                                    ,(
                                                                    self.mu_var.get(),
                                                                    self.add_var.get(),
                                                                    self.phone_var.get(),
                                                                    self.jn_var.get(),
                                                                    self.name_var.get(),
                                                                    self.num_var.get()
                                                                    ))
        con.commit ()
        self.fetch_all()
        self.clear()
        con.close ()
    Reply

  3. Antwon says

    Vishal,
    I tried to use your code to work in my scenario but continued to hit issues. I have a SQL table in PHPMyAdmin and using Python 3.8. I want to take a CSV file that matches a table in SQL wherein third party updates or replaces the CSV file on occasion. I want Python to check it every few hours for changes to a specific column. If a row has been changed, then Python would update the SQL table. My approach was to load the CSV file in a panda dataframe then conduct a df.to_sql to update the existing table.

    Here is my code:
    —-

    import pandas as pd 
import mysql.connector
import pymysql

df = pd.read_csv("circuits.csv")

mydb = pymysql.connect(host="localhost",user="my_opps",passwd="my_pass",database="my_db" )

mycursor = mydb.cursor()

sql = "SELECT ServiceStatus FROM carriercircuits WHERE CircuitServiceID = ?;"

mycursor.execute(sql)

mydb.commit()


df.to_sql('carriercircuits', mydb, if_exists='append', index=False)

print(mycursor.rowcount, "record(s) affected")
    

---
Thanks, 

Antwon
    Reply

  4. rehan says

    Hi Vishal,
    If I update multiple column by user input, I added values in some fields and blank some, If data already there in my column why it removed from there.

    Reply

  5. Viktoras says

    I just run over the code and noticed one thing in the section called:
    Use a Python variable in an Update query to update MySQL table

    It was used in example code:

    def updateLaptopPrice(id, price):
    
I just checked it, it believe it must be like this:

    def updateLaptopPrice(price, id):
    Reply

  6. Olgac says

    Hi Vishal,

    I am trying to get the data from arduino with raspberry pi and save to mysqle with serial communication. And I get an error like this:
    TypeError: Can’t convert ‘bytes’ object to str implicitly
    Have you encountered this error before?

    Reply

  7. Carlos says

    Hi Vishal,

    How can I use %s to set column_name?

    I mean, I would like to do something like “UPDATE table_name SET %s = %s WHERE id = id_value”

    But column_name should be like “ or not quotes (I think)

    I would be very grateful if you could help me

    Reply

  8. Murugan says

    when i Python update multiple Columns of MySQL table : 

    connection is closed
Traceback (most recent call last):
  File "updateDB_coloumn.py", line 9, in 
    cursor = connection.cursor(prepared=True)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/connection_cext.py", line 482, in cursor
    return (types[cursor_type])(self)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", line 820, in __init__
    "Alternative: Use connection.MySQLCursorPrepared")
NotImplementedError: Alternative: Use connection.MySQLCursorPrepared

    ERROR will occur

    Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your entire code </pre>

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Explore Python

Coding Exercises

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our:

Copyright © 2018–2026 pynative.com