UPDATE a MySQL table using Python and PyMySQL

Overview:

The SQL statement UPDATE is one of the DML – Data Manipulation Language statements, the other DML statements being the INSERT and DELETE.

The UPDATE statement updates one are more columns of data one or more rows. In other words, the UPDATE statement replaces old values of one or more cells of data belonging to several rows with new values. The old values are discarded.

The rows updated are based on the WHERE clause specified in the UPDATE statement.

Note that there are some database management systems, which support multiple versions of data of a cell, which is beyond the scope of this article.

In case if autocommit is not enabled while creating the database connection, the changes made to the database table need to be explicitly committed.

# import the python mysql client

import pymysql

# Create a connection object to the MySQL Database Server

ipOfHost = "127.0.0.1"

dbUser = "root"

dbUserPassword = ""

dbName = "test"

dbCharset = "utf8mb4"

cursorType = pymysql.cursors.DictCursor

databaseConnection = pymysql.connect(host=ipOfHost,

user=dbUser,

password=dbUserPassword,

db=dbName,

charset=dbCharset,

cursorclass=cursorType,

autocommit=True)

try:

# Cursor object creation

cursorObject = databaseConnection.cursor()

updateStatement = "UPDATE Employee set DepartmentCode = 102 where id=121"

# Execute the SQL UPDATE statement

cursorObject.execute(updateStatement)

# Select the updated row and print the updated column value

sqlSelectUpdated = "select * from Employee where id=121"

# Execute the SQL SELECT query

cursorObject.execute(sqlSelectUpdated)

# Fetch the updated row

updatedRow = cursorObject.fetchall()

# Print the updated row...

for column in updatedRow:

print(column)

except Exception as e:

print("Exeception occured:{}".format(e))

finally:

databaseConnection.close()

{'id': 121, 'LastName': 'Adams', 'FirstName': 'John', 'DepartmentCode': 102}