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.
- UPDATE statements can be executed from a Python program Using PyMySQL.
- In case if autocommit is not enabled while creating the database connection, the changes made to the database table need to be explicitly committed.
Example:
|
# 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() |
Output:
|
{'id': 121, 'LastName': 'Adams', 'FirstName': 'John', 'DepartmentCode': 102} |