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()
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()
Connection Successful :) Query Executed Successfully !!!
Happy Learning 🙂