Getting Started

Python - IntroductionPython - Hello World ProgramPython - SyntaxPython - Data TypesPython - Variables

Operators

Python - Arithmetic OperatorsPython - Relational OperatorsPython - Logical OperatorsPython - Assignment OperatorsPython - Bitwise OperatorsPython - Membership OperatorsPython - Identity OperatorsPython - Increment and Decrement Operators

Conditions

Python - If Else statement

Loop

Python - While LoopPython - For Loop

Numbers

Python - NumbersPython - Number Conversion

Strings

Python - StringsPython - String OperatorsPython - String FormattingPython - String MethodsPython - String Format Method

List

Python - ListPython - List Methods

Tuple

Python - Tuple

Set

Python - SetPython - Set Methods

Dictionary

Python - DictionaryPython - Dictionary Methods

Functions

Python - FunctionsPython - Functions - Variable length argumentsPython - Lambda Function

Scope of Variables

Python - Scope of Variables

Modules

Python - ModulesPython - Math ModulePython - JSON ModulePython - datetime ModulePython - time Module

I/O

Python - Read input from keyboard

File

Python - File Handling

Exception Handling

Python - Exception Handling

OOP

Python - Classes and ObjectsPython - Class Constructor __init__ methodPython - Class Destructor __del__ methodPython - Built-in Class AttributesPython - InheritancePython - Method OverridingPython - Method Overloading

Package Management

Python - PIP

Python - MySQL

Python - MySQL - Getting StartedPython - MySQL - Insert dataPython - MySQL - Select dataPython - MySQL - Update dataPython - MySQL - Delete data

Python - CSV

Python - Read data from CSV filePython - Write data in CSV file

Python - MySQL - Update data

Python

python logo

In this tutorial we will learn to update data in MySQL database using Python.

We have covered how to insert data into MySQL table using Python and how to fetch data from MySQL table using Python in the previous tutorials. Feel free to check that out.

Prerequisite

To understand this tutorial you need to have some prior knowledge of MySQL.

Note!

For complete MySQL tutorial click here.

To learn more about UPDATE query check out this MySQL - UPDATE Table tutorial.

Update table data

To update data from a table we have the following steps.

  1. Connect to the MySQL server using the mysql.connector.connect() method.
  2. Create MySQL cursor from the connection created in step 1.
  3. Use the cursor to execute the UPDATE query.
  4. Use the commit() method to save the changes.
  5. Close connection.

In this tutorial we will be working with an employee table.

The table already has some rows which are shown below.

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2018-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2018-01-05 13:14:15 |
| e08        | Doctor    | Who      | 2018-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)

Update record

To update records in a table we take help of the execute() method and pass SQL query and the values that we want to update.

In the following Python program we are updating the firstname of the employee having id e08.

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:

    cnx = mysql.connector.connect(
        user='yusufshakeel',
        password='',
        host='127.0.0.1',
        database='mydb'
    )

except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')

# update data
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # placeholder
    firstname = "Dr."
    employeeid = "e08"
    
    # sql query
    query = "UPDATE employee SET firstname = %s WHERE employeeid = %s"
    
    # data
    data = (firstname, employeeid)
    
    # execute
    cnxCursor.execute(query, data)
    
    # commit
    cnx.commit()
    
    # total number of rows updated
    print("Total rows updated: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    
    print("Error:", err.message)

except:

    print("Unknown error occurred!")

finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

On success the above code will give us the following output.

Total rows updated: 1

If we check the employeetable we will get the following output.

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2018-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2018-01-05 13:14:15 |
| e08        | Dr.       | Who      | 2018-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)