In this tutorial you will learn how to add data in database using python code, insert data into mysql database in python.
First, we need to setup database username, password in configuration file, and then read from there!
Read Data 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.mydb = mysql.connector.connect( host=config.get('dbinfo', 'dbhost'), user=config.get('dbinfo', 'dbusername'), password=config.get('dbinfo', 'dbpassword'), database=config.get('dbinfo', 'database'))
In above step, we have configured mysql database credentials, now we are ready to insert data to database.
Write a function with name "AddStudent" (you can give any name), then create mysql cursor object like mycursor = self.mydb.cursor()
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 AddStudent(self): try: mycursor = self.mydb.cursor() today=date.today() tdate = today.isoformat(); #print(tdate) sql_insert_query="insert into tbStudent (firstName, LastName, RegDate) values ('Mike','Gates','"+ tdate +"')"; mycursor.execute(sql_insert_query) self.mydb.commit(); print("New Student Added") except mysql.connector.Error as error: print("Failed to insert query into tbStudent table {}".format(error)) finally: mycursor.close()
Make sure you close the cursor after use, like mycursor.close()
Note: in above example we have inserted hardcoded data from python script just for your understanding. Now we see how add dynamic data, means data from python class object.
Now let's write the same above function little differently, you can learn following two things from example below.
def AddStudent(self, student): try: mycursor = self.myConnection.cursor() today=date.today() tdate = today.isoformat(); sql_insert_query="insert into tbStudent (firstName, LastName, RegDate) values ('{}','{}','{}')"; sql_insert_query =sql_insert_query.format(student.first_name, student.last_name, today); mycursor.execute(sql_insert_query) self.myConnection.commit(); print("New Student Added") except mysql.connector.Error as error: self.myConnection.rollback(); print("Failed to insert query into tbStudent table {}".format(error)) finally: mycursor.close() print("Added 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.AddStudent(s);
After inserting you can check if data inserted properly, learn how to read from mysql database
Python mysql db batch insert: If you want to multiple values (rows), then either you can loop through the collection and insert one by one using execute method or you can use executemany method like example below.
inserData = [ ('Anu', 'email1@doamin.com'), ('Ajay', 'email2@doamin.com'), ('Arnav', 'email3@doamin.com'), ] stmt = "INSERT INTO tbStudents (fullName, emailId) VALUES (%s, %s)" cursor.executemany(stmt, inserData)
Hope above example will help you to understand the multiple row insert from python code, make sure you form the query rightly.