Python mysql insert example

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!

Step 1

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.

Add data to mySql using Python

Step 2

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.

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

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

executemany method for multiple row insert

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.

 
Python MySql Insert example
Learn python programming with free python coding tutorials.
Other Popular Tutorials
Add data to MySql using Python
Python programming examples | Join Python Course