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.

How to use INSERT query in Python

Import mysql.connector
This will connect your project/program to Python’s MySQL module so that your work can communicate to your database.
Use the connect() method
Using mysql.connector.connect() will establish a connection to your database. You can set the host, user, password, and database name as parameters of this method.
Preparing the SQL
Next is to prepare your query to insert values into your database. The values inserted to your table should be in the same order as the columns. If you want, you can enumerate the columns on the SQL but the values should be in the same order. Create an appropriately named variable to store your SQL. We will use this later as a parameter.
The cursor() method
This will create a cursor object that will interact with database.
Execute the SQL with execute()
Use your SQL variable as parameter for the execute method.
Commit
Using the commit() method will send the data to the MySQL server. This will permanently change the database. This step is important because Python does not autocommit. So after every execution, you must use the commit() method.
Closing the connection
Lastly, close the connection of your database.

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:

Inquiries

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