Python - Update Records in MySQL Data Database

In this tutorial, we’ll show see how to update records in MySQL Database using Python step by step.

1. Setup Required to connect MySQL

To set up the environment, we need to connect MySQL with Python, go through this tutorial to see how to connect MySQL using mysql connector.

If you follow along with the tutorial link, we have connected everything and have code below in our IDE.

# Importing the MySQL-Python-connector
import mysql.connector as mysqlConnector
# Creating connection with the MySQL Server Running 
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')
# Checking if connection is made or not
if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")
# Creating a cursor object to traverse the resultset
cur = conn.cursor()
cur.execute("SHOW DATABASES")
for row in cur:
    print(row)
# Closing the connection
conn.close()

Output:

Connection Successful 
(‘information_schema’,)
(‘mysql’,)
(‘performance_schema’,)
(‘sys’,)

2. Update Python MySQL DB:

Use-case – A student having std_id =10 has name shubham, not shivam as in the database and we have to update that using SQL queries in Python. We will fix that using the UPDATE <table-name> SQL Query. This will set the name ‘shubham’ where std_id=10. Now because we have made modifications, it is important to commit the changes to reflect in the database permanently.

import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("update students set name='shubham' where std_id=10")
    print("Query Executed Successfully !!!")
    conn.commit()
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful :)
Query Executed Successfully !!!

3. References:

Happy Learning 🙂