Insert into MySQL Table Using Python

Here we will insert data into MySQL table using Python script. To insert data, we will use SQL insert query and built-in methods of Python MySQL connector.

Python MySQL Connector is a driver that acts as a bridge to establish connection between Python and MySQL. For more details, you can refer our previous topics.

Here we will insert data by using two main methods which are used to insert data. It means we can insert single row or multiple rows into the table. Lets see the methods details:

• execute(): It is used to execute SQL query or command. we can pass SQL query as an argument.

• executemany(): It is also used to execute SQL query and it can be used to insert multiple rows into the table. 

Now lets see examples of each method to understand in better manner.

Python Example: Insert single row into MySQL table

To insert data into MySQL table, we used execute() method that is used to execute SQL query. Here we used this method to insert single row into the table.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySQL
  con = mysql.connector.connect(user='username', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # Sql query to insert date into table
  sql_query = '''INSERT INTO EMPLOYEE(EMP_ID,
   FIRST_NAME, LAST_NAME, JOB_PROFILE)
   VALUES ('E20021','John', 'Polo', 'Programmer')'''

  # Executing the SQL command
  cursor.execute(sql_query)

  # Commit your changes in the database
  con.commit()

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    con.rollback()
    print(err)
else:
  con.close()

Output:

The above code will insert a row into EMPLOYEE table which you can verify by login into your mysql database.

Python Example: Parameterized query  into MySQL table

In this example, we are using parameterized query to insert data into table. It is helpful when we don't want to pass values in the SQL query. See the below example.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='username', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to INSERT a record into the database.
  sql_query = (
  "INSERT INTO EMPLOYEE(EMP_ID, FIRST_NAME, LAST_NAME, JOB_PROFILE)"
  "VALUES (%s, %s, %s, %s)"
  )
  
  values = ('E23005', 'Somya', 'Shri', 'Designer')

  # Executing query
  cursor.execute(sql_query, values)

  # Commit your changes in the database
  con.commit()

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    con.rollback()
    print(err)
else:
  con.close()

Python Example: Inserting Multiple rows into MySQL

Sometimes we want to insert multiple rows in our table then using execute() method multiple times is not good idea. In that case we used executemany() method that is used to insert multiple rows into the table.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='username', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # SQL query to INSERT a record into the database.
  sql_query = (
  "INSERT INTO EMPLOYEE(EMP_ID, FIRST_NAME, LAST_NAME, JOB_PROFILE)"
  "VALUES (%s, %s, %s, %s)"
  )
  
  values = [('E23006', 'Rohan', 'Som', 'Linux Admin'),
	    ('E23007', 'David', 'Parker', 'Developer')
           ]

  # Executing query
  cursor.executemany(sql_query, values)

  # Commit your changes in the database
  con.commit()

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    con.rollback()
    print(err)
else:
  con.close()

Conclusion

Well, in this topic, we learnt to insert data into MySQL table by using execute and executemany() methods.