Python Update MySQL Database Table

This article demonstrates how to issue a MySQL UPDATE query from python to update MySQL database table’s data.

Goals of this lesson:

After reading this article you can do the following MySQL update operations using Python.

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

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 computers table present in my MySQL server.

Python MySQL Update Example to update a record of a table

To perform a SQL UPDATE query from Python, you just need to follow these simple steps: –

• Install MySQL Connector Python using pip.
• Create a MySQL database connection in python
• Prepared the Update statement query (here you must know the table’s columns details)
• Execute the UPDATE query, using cursor.execute()
• Close the cursor object and database connection
• Catch any SQL exceptions that may occur during this process

Let’s see the program now. In this program, we are updating a “computer” table by changing the value of ram column of the computer whose id is 1.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
   conn = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')
   cursor = conn.cursor()

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

   #Update single record now
   sql_update_query = """Update computers set ram = 10 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 record to database: {}".format(error))
    connection.rollback()

finally:
    #closing database connection.
    if(conn.is_connected()):
        conn.close()
        print("connection is closed")

You should get the following output after execution of the above code: –

Before updating record 
(1, 'Apple', datetime.date(2018, 1, 3), 15, 'I5')
Record Updated successfully 
After updating record 
(1, 'Apple', datetime.date(2018, 1, 3), 10, 'I5')
connection is closed

Let’s Understand the above example now

•  import mysql.connector  this line imports MySQL Connector Python module in your program so you can use this modules API to connect MySQL
• Next, We used the mysql.connector.connect()  function to connect the MySQL Database from Python.
• Then we prepared SQL UPDATE query to update a “ram” column of a computer table. Update query contains the column value to be updated.
• we used a connection.cursor() function to get cursor object from the connection. this method creates a new MySQLCursor object. using a cursor.execute() we can execute the operation stored in the UPDATE query.
• Make changes persistent into a database using connection.commit().
• After a successful update operation, use a cursor.rowcount method to get the number of rows affected. here it will return 1 because we are updating one row.

Use a Python variable in an Update query to update MySQL table

Read more on What is a Parameterized Query and its performance benefits.

Let’ s see the example program now.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   cursor = connection.cursor(prepared=True)

   #Update single record now
   sql_update_query = """Update computers set ram = %s where id = %s"""
   ram = 20
   id = 2
   input = (ram, id)
   cursor.execute(sql_update_query, input)
   connection.commit()
   print("Record Updated successfully with prepared statement")

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

finally:
    #closing database connection.
    if(connection.is_connected()):
        connection.close()
        print("connection is closed")

Output: –

Record Updated successfully with prepared statement
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 update query one for “ram” column and other is for the” id” column
• We then added those two columns value in the input tuple in sequential order and passed SQL update query and input tuple t0 cursor.execute() function,  remember tuple contain user data in sequential order of placeholders.
• In the end, we are committing our changes to the database using connection.commit()

Python MySQL update Multiple Rows data in a single query

It is possible to update multiple rows in a single SQL Query. I have prepared an example which demonstrates the same. you can also call it a bulk update. We can use a cursor.executemany() method of cursor object to update multiple tables rows.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   cursor = connection.cursor(prepared=True)

   #Update query  rows
   sql_update_query = """Update computers set ram = %s where id = %s"""

   # multiple records to be updated in tuple format
   records_to_update = [ (30, 1), (15, 2) ]

   cursor.executemany(sql_update_query, records_to_update)
   connection.commit()
   print(cursor.rowcount, "Records Updated successfully into computers table. ")
   print("The Updated count is: ", cursor.rowcount)

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

finally:
    #closing database connection.
    if(connection.is_connected()):
        connection.close()
        print("connection is closed")

Output:

2 Records Updated successfully into computers table. 
The updated count is:  2
connection is closed

Let’s  understand the above program

• cursor = connection.cursor(prepared=True) function allows a prepared statement to execute a SQL query.
• We defined a SQL update query with two placeholders (“ram” and “Id” column ). and prepared List of records to be updated. this List contains tuple for each row. here we created two tuples so we are updated two rows.
• We used the cursor.executemany()function to update multiple rows of a database table.
• using  cursor.rowcount we can find how many rows are actually updated successfully.

The syntax of the executemany() method

cursor.executemany(operation, seq_of_params)

This method prepares a database operation (i.e. query or command) and executes it against all parameter sequences in the sequence “seq_of_params” argument.

Python update multiple Columns of MySQL table

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

# write MySQL connection code. you can refer example in the scetion one

cursor = connection.cursor(prepared=True)
#Update query to update multiple columns
sql_update_query = """Update computers set name = %s, ram = %s processor = %s where id = %s"""
name = "DELL"
ram = 30
processor = "i7"
id = 2
input = (name, ram, processor, id)
cursor.execute(sql_update_query, input)
connection.commit()
print("multiple columns Updated successfully ")

# close the cursor and connection object. 

Output:

multiple columns Updated successfully

Python Update Datetime and timestamp column of a MySQL table

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

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from datetime import datetime
try:
   connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

   cursor = connection.cursor(prepared=True)

   sql_update_query = """Update computers set purchased_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 to database: {}".format(error))

finally:
    #closing database connection.
    if(connection.is_connected()):
        connection.close()
        print("connection is closed")

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

purchased Date Updated successfully 
connection is closed

Next Steps:

To practice what you learned in this article, I have created a Python Database programming Quiz and Exercise Project.

That’s it. Folks Let me know your comments and questions in the section below.