Free Web Development Online Tutorials, Learn to Code
Python mysql insert example, insert data to mysql, Python MySql Development Guide

Python mysql insert example

In this tutorial you will learn how to insert data into mysql database in python.

Add data to mySql using Python

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


Python MySql Insert example

Add data to MySql using Python
Python programming examples | Join Python Course