Python MySql Update Table

Home > Python Tutorial > Python MySql Update Table



To Update a Table

This is the python mysql update module of our entire python MySQL tutorial. And while using this programming language, If you wish to update any particular table then you can easily do that.

To accomplish this task it is recommended that you should use the “UPDATE” statement. If you wish to make some changes in the address column by changing the address from ‘Valley 345’ to ‘Canyon 123’ then the python update sql server table example for this is mentioned below.  

import mysql.connector

mydb = mysql.connector.connect (

host=“localhost” ,

user=“yourusername” ,

passwd=“yourpassword” ,

database=“mydatabase” ,

)

mycursor = mydb.cursor ( )

sql = “UPDATE customers SET address = ‘Canyon 123’ WHERE address = ‘Valley 345’ ”

mycursor.execute ( sql )

mydb.commit ( )

print ( mycursor.rowcount, “record ( s ) affected” )

  It is important for you to focus on the statement of mydb.commit ( ). The focus is required on this statement because of the fact that if you fail to use this statement then you won’t be able to make any changes in the python sql update table at all.

It is always important for you to remember that to make any particular changes in a table you need to use this statement. You should also focus on the WHERE clause of the UPDATE syntax.

This clause that is used here plays a very important role as this clause helps in specifying the record that needs to be updated. If you fail to use this clause then all of the records will be updated and changed.  

To Prevent SQL Injection

As we have mentioned before this python sql update table tutorial module, that it is always a good idea to escape a query value.

This particular statement is also true for the case of the update statements. This particular step is taken so any kind of SQL injection that might occur can be avoided.

This SQL injection that we just mentioned is nothing but a common web hacking technique.

This web hacking technique is used by hackers from all across the world to misuse or destroy the data that you have stored in your database.

It is also important for you to remember that in the mysql.connector module you need to use the placeholder %s to escape any kind of value when it comes to the delete statement.

The python update sql server table example for this is mentioned below.  

import mysql.connector

mydb = mysql.connector.connect (

host=“localhost” ,

user=“yourusername” ,

passwd=“yourpassword” ,

database=“mydatabase” ,

)

mycursor = mydb.cursor ( )

sql = “UPDATE customers SET address = %s WHERE address = %s ”

val = ( “Valley 345”, “Canyon 123” ) 

mycursor.execute ( sql, val )

mydb.commit ( ) 

print ( mycursor.rowcount, “record ( s ) affected” )

With this, we finish the Python mysql update part of our entire Python MySQL tutorial.