Python mysql update example

In this tutorial you will learn how to update mysql database in python code.

First, we need to setup database username, password in configuration file, and then read from there!

Step 1

Read database configuration information from "mypy.ini" configuration file. You can look at how to read configuration from .ini file

Create a new instance of config = ConfigParser(), at the time of initialization of class.

Create a new instance of mysql.connector.connect, and set all database credentials

class StudentDTO():        
    def __init__(self):
        config = ConfigParser()
        config.read('mypy.ini')
        database = config.get('dbinfo', 'database')
        dbusername = config.get('dbinfo', 'dbusername')
        dbpassword = config.get('dbinfo', 'dbpassword')
        dbhost = config.get('dbinfo', 'dbhost')
         
        self.myConn = mysql.connector.connect(
          host=config.get('dbinfo', 'dbhost'),
          user=config.get('dbinfo', 'dbusername'),
          password=config.get('dbinfo', 'dbpassword'),
          database=config.get('dbinfo', 'database'))

Update mySql data using Python

Step 2

Write a function with name "UpdateStudent" (you can give any name), then create mysql cursor object like mycursor = self.myConn.cursor() from connection object that we have created at the time of initialization of class in above code

Then execute your sql query like mycursor.execute("sql query")

After inserting data you must commit changes in database object like self.mydb.commit();

Make sure you have implemented error handing, so you can catch the right error, in case anything goes wrong.

     except mysql.connector.Error as error:

def UpdateStudent(self):
    try:
        #self.myConnection.autocommit = false 
        mycursor = self.myConnection.cursor(buffered=True)     
          
        today=date.today()
        tdate = today.isoformat();     
     
        sql_query1="update tbStudent set firstName='Ajay', LastName='Sanyal' where StuId=6";
        sql_query2 ="update tbStudent set Mobile='9800000', City='Kolkata' where StuId=6";  
          
        mycursor.execute(sql_query1)
        mycursor.execute(sql_query2)

        self.myConnection.commit();
        print("Student information updated")
       
    except mysql.connector.Error as error:
        print("Failed to insert query into tbStudent table {}".format(error))
        self.myConnection.rollback();
        
    finally:
        mycursor.close()
        print("Updated Successfully")

Make sure you close the cursor after use, like mycursor.close()

Note: in above example we have updated hardcoded data from python script just for your understanding. Now we see how add dynamic data, means data from python class object.

Also notice, how transaction has been implemented and python mysql connector update statement, we have two SQL statements in above example, so either both will get saved, or both fail, transaction property is always associated with connection object.

      mycursor.execute(sql_query1)
      mycursor.execute(sql_query2)
      
    try:
        self.myConnection.commit();

    except mysql.connector.Error as error:        
        self.myConnection.rollback();

Now let's write the same above function little differently, you can learn following two things from example below.

how to pass python object as parameter, so instead of hard coding values, you can pass and object new different values
How to form a SQL string using format method! (which is considered as best practice too.)

def UpdateStudent(self, student):
    try:
        #self.myConnection.autocommit = false 
        mycursor = self.myConnection.cursor(buffered=True)     
          
        today=date.today()
        tdate = today.isoformat();          
        sql_query1="update tbStudent set firstName='{}', LastName='{}', RegDate='{}', email='{}' where StuId={}";
        sql_query1 =sql_query1.format(student.first_name, student.last_name, today, student.Email, student.StuId);  
          
        mycursor.execute(sql_query1)
        self.myConnection.commit();
        print("Student information updated")
       
    except mysql.connector.Error as error:
        print("Failed to insert query into tbStudent table {}".format(error))
        self.myConnection.rollback();
        
    finally:
        mycursor.close()
        print("Updated Successfully")

Now to call the above function, we can create a new Student object and then pass that object as parameter.

    dto=StudentDTO();
    
    s = Student();
    s.StuId=8;
    s.first_name = "Bichitra";
    s.last_name = "Chakraborty";
    s.Mobile = "900000015";
    s.Address = "A / Add / 22 On Road";
    s.Email = "bichitra@gmail.com" ;
    dto.UpdateStudent(s);

After updating you can check if data updated properly, learn how to read from mysql database

Python Tutorial | Python Course | Python Interview Questions

Python MySql Update example

Learn python programming with free python coding tutorials.