Python MySQL INSERT Query: Step by Step Guide in Python

Python MySQL INSERT Query: Step by Step Guide in Python

In this article, you will learn how to use Python MySQL INSERT query to add data to your database. If you have a background in SQL, this tutorial will improve your knowledge in handling your database in Python.

Prerequisite

First things first – You need to set up your project. If in case you do not know how to do it, click the link to go to our step by step guide.

Also, if you have any trouble in using the PIP command, just click the link to go to our easy step by step fix. Make sure you have a database to work with.

Sample Code

Let us take a look at the example code. The database used here is “python_db” with a “students” table.

import mysql.connector
import mysql.connector.errors

try:
    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

    sqlInsertQuery = "INSERT INTO students VALUES (20200011, 'Rodrigo', 'Go', 'Roque')"
    dbCursor = dbConnection.cursor()
    dbCursor.execute(sqlInsertQuery)
    dbConnection.commit()
    print(dbCursor.rowcount, "Record/s successfully added into students table.")
    dbCursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into students table {}".format(error))

finally:
    if (dbConnection.is_connected()):
        dbConnection.close()
        print("MySQL connection is closed")

Output

1 Record successfully added into students table.
MySQL connection is closed

Process finished with exit code 0

Query Variation

You can also make this as a parameterized query. For this example, we have a “insertStudentRecord” function with “id”,”fn”,”mn” and “ln” as parameters.

import mysql.connector
import mysql.connector.errors

# ------ Parameterized Query------

def insertStudentRecord(id, fn, mn, ln):

    try:
        dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

        sqlInsertQuery = "INSERT INTO students (studentID, fName, mName, lName) VALUES (%s, %s, %s, %s)"
        dbCursor = dbConnection.cursor()
        studentData = (id, fn, mn, ln)
        dbCursor.execute(sqlInsertQuery, studentData)
        dbConnection.commit()
        print(dbCursor.rowcount, "Record/s successfully added into students table.")
        dbCursor.close()

    except mysql.connector.Error as error:
        print("Failed to insert record into students table {}".format(error))

    finally:
        if (dbConnection.is_connected()):
            dbConnection.close()
            print("MySQL connection is closed")


insertStudentRecord(20200012, "John","Medina", "Martin" )

You will get the same output as before.

MySQL INSERT Multiple Row Insertion

To insert multiple rows, you will use the executemany() function. With this, you can use an array containing your multiple records to pass to this function. Take a look at the example below.

import mysql.connector
import mysql.connector.errors

try:

    dbConnection = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="python_db")

    sqlInsertQuery = "INSERT INTO students (studentID, fName, mName, lName) VALUES (%s, %s, %s, %s)"
    dbCursor = dbConnection.cursor()
    studentBatchData = [(20200013, 'Marian', 'Ignacio', 'Rivera'),
                        (20200014, 'Clarence', 'Yulo', 'Dimagiba'),
                        (20200015, 'Justin', 'Vidal', 'Martin')]
    dbCursor.executemany(sqlInsertQuery, studentBatchData)
    dbConnection.commit()
    print(dbCursor.rowcount, "Record/s successfully added into students table.")
    dbCursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into students table {}".format(error))

finally:
    if (dbConnection.is_connected()):
            dbConnection.close()
            print("MySQL connection is closed")

You will get an output like this:

3 Record/s successfully added into students table.
MySQL connection is closed

Process finished with exit code 0

Conclusion

That’s how you use Python MySQL INSERT query in your projects. You can always expand and try different ways in implementing the INSERT statement in your Python projects. Check out the different Python – related projects below:

• Python MySQL SELECT Query Guide
• Hotel Management System in Python
• Patient Information System in Python
• Hospital Management System in Python
• Library Management System in Python
• Student Management System in Python

Inquiries

If you have any questions or suggestions about the python mysql insert query, please feel free to leave a comment below.