- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can you update certain values in a table in MySQL using Python?
The data in the table may be outdated and we may require to change the data after some time. Suppose, we have a table of Students and one of the students have changed their address. We require to change the address of the student in the database to avoid any problems in future due to wrong data.
The “UPDATE” statement in MySQL is used to update some value in the table.The SET clause is used to set the new value in the column. The WHERE clause is used to identify where in the table do we need to update the data or value.
Syntax
UPDATE table_name SET column=new_value WHERE column=old_value
Steps invloved to update data in a table using MySQL in python
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
commit the changes made using commit() method
close the connection
Suppose we have a table named “Student” as follows −
+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+
Example
Suppose, we have the above table of students and we want to update the city of Kriti from Batala to Kolkata.
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="UPDATE Students SET City='Kolkata' WHERE Name='Kriti'" cursor.execute(query) db.commit() query="SELECT * FROM Students" cursor.execute(query) for row in cursor: print(row) db.close()
The above code updates the city name of Kriti.
Output
(‘Karan’, 4 ,’Amritsar’ , 95) (‘Sahil’ , 6 , ‘Amritsar’ ,93) (‘Kriti’ , 3 , ‘Kolkata’ ,88) (‘Amit’ , 9 , ‘Delhi’ , 90) (‘Priya’ , 5 , ‘Delhi’ ,85)
NOTE
The db.commit() in the above code is important. It is used to commit the changes made to the table. Without using commit(), no changes will be made in the table.
- Related Articles
- How can we update values in a MySQL table?
- How can we update the values in one MySQL table by using the values of another MySQL table?
- How can we update any value in MySQL view as we can update the values in MySQL table?
- How can you delete a record from a table using MySQL in Python?
- How can I create a stored procedure to update values in a MySQL table?
- How can you delete a table from a database in MySQL Python?
- How can you select data from a table based on some criteria using MySQL in Python?
- How can you test if some record exists or not in a MySQL table using Python?
- How can I update the boolean values in MySQL?
- Update multiple values in a table with MySQL IF Statement
- How to update a MySQL table by swapping two column values?
- How can I update a table using prepare statements?
- How can we update MySQL table after removing a particular string from the values of column?
- How can we update MySQL table after padding a string with the values of the column?
- How can I update MySQL table after quoting the values of a column with a single quote?
