How to insert record in MySql using Python

In this post we will learn how to execute the INSERT Query using Python to add record into table.

For insert record we will use ‘MySQL Connector’ module.

Create Database and Table : First create a database and table for insert records

For creating database connection and table using python follow below post

How to connect MySQL Database in Python – BlogsHub

Insert a single record into table from Python.

Prepare Insert Query: First prepare an insert query to insert a record into a table. using the insert query, we have to mention the column names and the values to insert in a table.

ex: insert into table_name (table_column1, table_column2, table_column3, …) values (value1, value2, value3, …)

Create Connection Object: Now create object using connection.cursor() method . Using connection.cursor method we can create a new connection cursor object.

Run or Execute Query : Once the connection object has created, we have to execute the insert query using the cursor.execute() method.

Commit the Changes: After execution of the query use commit method of connection class for make changes persist into database.

Connection Close: For connection close we can use cursor.clsoe() and connection.clsoe() method once the query execution has committed.

import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    mySql_insert_query = """INSERT INTO User (Name, Email) VALUES ('User1', 'user1@gmail.com') """

    cursor = connection.cursor()
    cursor.execute(mySql_insert_query)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully")
    cursor.close()
except mysql.connector.Error as error:
    print("Error:insertion error {}".format(error))

finally:
    if connection.is_connected():
        connection.close()
        print("Connection closed")

Use Python Variables in a MySQL Insert Query

Insert Records Using Python Variables

We can get the value to user and insert in database when the user will submit the form we can get that value and insert using Python variables with prepared statement,

we have to pass these variables as a query parameter.

import mysql.connector

def insertwithvarables(name, email):
    try:
        connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
        cursor = connection.cursor()
        mySql_insert_query = """INSERT INTO User (Name, Email) VALUES (%s, %s) """
        record = (name, email)
        cursor.execute(mySql_insert_query, record)
        connection.commit()
        print("Record insert successfully")

    except mysql.connector.Error as error:
        print("Error:insertion error {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("Connection closed")

insertwithvarables('User2','user2@gmail.com')
insertwithvarables('User3','user3@gmail.com')
insertwithvarables('User4', 'user4@gmail.com')

How to insert multiple records in table using the cursor’s executemany method

We can use the cursor executemany Method for insert multiple records into a table using python.

import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    mySql_insert_query = """INSERT INTO User (Name, email) VALUES (%s, %s) """
    records = [('Newuser1', 'Newuser1@gmail.com'),('Newuser2','Newuser2@gmail.com'),('Newuser3','Newuser2@gmail.com')]

    cursor = connection.cursor()
    cursor.executemany(mySql_insert_query, records)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully!")

except mysql.connector.Error as error:
    print("Error:insertion error {}".format(error))

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("Connection closed")

Keep Learning 🙂