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!
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'))
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.
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