Python Insert Into MySQL Table

This article demonstrates how to execute INSERT Query from python to add a new row into MySQL table.

Goals of this lesson: You’ll learn the following MySQL insert operations from Python.

• Insert single and multiple rows into MySQL table.
• Insert Integer, string, float, double,  and DateTime variable into a MySQL table.
• Use a parameterized query to insert Python variables and dynamic data into a table.

Prerequisite

Before moving further, Please make sure you have the following in place: –

• Username and password that you need to connect MySQL
• MySQL table name in which you want to insert data.

I have created a table “Laptop” in MySQL server to insert records in it. See its column structure in the image.

If no table is present in your MySQL server you can refer to our article to create a MySQL table from Python. Also, you can download Laptop table creation in MySQL file which contains SQL query to create a Laptop table so you can use this table for your INSERT operations.

Note: We are using the MySQL Connector Python module to insert data into a MySQL table.

Python example to insert a single row/record into MySQL table

To perform a SQL INSERT query from Python, you need to follow these simple steps:

• Install MySQL Connector Python using pip.
• First, Establish a  MySQL database connection in Python.
• Then, Define the SQL INSERT Query  (here you need to know the table’s column details).
• Execute the INSERT query using the cursor.execute()and get a number of rows affected.
• After the successful execution of a query, Don’t forget to commit your changes to the database.
• Close the MySQL database connection.
• Most important, Catch SQL exceptions if any.
• At last, verify the result by selecting data from the MySQL table.

Let’ s see the program now

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='root',
                                         password='pynative@#29')
    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                           VALUES 
                           (10, 'Lenovo ThinkPad P71', 6459, '2019-08-14') """

    cursor = connection.cursor()
    cursor.execute(mySql_insert_query)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into Laptop table")
    cursor.close()

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

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

You will get below output after executing the above program.

Record inserted successfully into Laptop table
MySQL connection is closed

Refer to How to fetch rows from MySQL table in Python to check the data that you just inserted.

Understand the Python MySQL insert example in detail

1. At first, import mysql.connector  imports MySQL Connector Python module in your program so you can use this module API to connect the MySQL database.
2. Next, We used mysql.connector.connect()  method to connect the MySQL Database from Python
3. Next, we prepared a SQL INSERT query to insert a row into a table. in the insert query, we mentioned column names and their values to insert in a table.
4. Next, Using a connection.cursor  method we created a cursor object, This method creates a new MySQLCursor object.
5. We are using the cursor.execute() method of cursor objects to execute the operation stored in the INSERT query. Also, using cursor.rowcount we can find out the number of rows affected.
6. At last, we are closing cursor and connection object inside the finally block once we complete insert operation. It is always good practice to close connections to avoid database issues.

Using Python variables in a MySQL INSERT query

Sometimes you need to insert a python variable value into a table’s column.  For example, in the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

• We can insert Python variables into the table using the prepared statement and parameterized query.
• Using a parameterized query we can pass python variables as a query parameter in which placeholders (%s) used for parameters.

You can read more on What is a Parameterized Query and its performance benefits. Let see how to do this with an example program.

import mysql.connector
from mysql.connector import Error

def insertVariblesIntoTable(id, name, price, purchase_date):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='Electronics',
                                             user='pynative',
                                             password='pynative@#29')
        cursor = connection.cursor()
        mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                                VALUES (%s, %s, %s, %s) """

        recordTuple = (id, name, price, purchase_date)
        cursor.execute(mySql_insert_query, recordTuple)
        connection.commit()
        print("Record inserted successfully into Laptop table")

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

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

insertVariblesIntoTable(2, 'Area 51M', 6999, '2019-04-14')
insertVariblesIntoTable(3, 'MacBook Pro', 2499, '2019-06-20')

Output:

Record inserted successfully into Laptop table
MySQL connection is closed
Record inserted successfully into Laptop table
MySQL connection is closed

Refer to How to fetch rows from MySQL table in Python to check the data that you just inserted.

Python Insert multiple rows into the MySQL table using the cursor’s executemany()

What if you want to insert multiple rows into the table in a single insert query from the Python application. You can insert multiple rows into the MySQL table. You can use the cursor’s executemany() function to insert multiple records into a table.

Note:- in the previous example we have used execute () method of cursor object to insert a single record.

The syntax of the executemany() method.

cursor.executemany(operation, seq_of_params)

This method prepares a database operation (i.e., query or command) and executes it against all parameter sequences in the sequence seq_of_params argument.

You need to include lists of tuples in the “seq_of_params” argument along with the insert query.

Each tuple inside the list contains a single row that you want to insert. So you can add as much as rows in the list and pass a list to cursor.executemany() function along with the insert query.

Here we are using the prepared statement (parameterized query) to insert multiple rows into a table.

Why use a prepared statement?

• Because to repeatedly execute the same statement with different data we use the prepared statement.
• Each tuple is enclosed within parentheses and separated by commas. For example, to insert multiple rows in a laptop table, we can use the following SQL Query:

INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s)

And in “seq_of_params” we are passing below List.

records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),
                     (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),
                     (6, 'Microsoft Surface', 2330, '2019-07-23')]

Let’s demonstrate this with an example.

Python example to INSERT multiple rows into a MySQL table

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                           VALUES (%s, %s, %s, %s) """

    records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),
                         (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),
                         (6, 'Microsoft Surface', 2330, '2019-07-23')]

    cursor = connection.cursor()
    cursor.executemany(mySql_insert_query, records_to_insert)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into Laptop table")

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

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

Output:

3 Record inserted successfully into Laptop table
MySQL connection is closed

Refer to How to fetch rows from MySQL table in Python to check the data that you just inserted.

Let’s understand the above program in detail: –.

1. After a successful database connection, we prepared a list of rows to insert into the table.
2. SQL INSERT statement contains the parameterized query which uses the placeholder (%s) for each column value.
3. connection.cursor()  to execute the prepared statement.
4. Next, Using cursor.executemany(sql_insert_query, records_to_insert)we are inserting multiple rows (from a List) into the table.
5. Using cursor.rowcount method we can find the number of records inserted. Finally, we are committing our changes to the database.

You can refer to our article on how to fetch data from MySQL table to verify your result.

Python Insert timestamp and DateTime into a MySQL table

For example, you have a date column in a MySQL table, and you want to insert a datetime.datetime() object into this column. Then how to prepare an insert query to add DateTime into a table column. Let’s see this with an example.

import mysql.connector
from mysql.connector import Error
from datetime import datetime

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) 
                            VALUES (%s, %s, %s, %s) """

    cursor = connection.cursor()
    current_Date = datetime.now()
    # convert date in the format you want
    formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
    insert_tuple = (7, 'Acer Predator Triton', 2435, current_Date)

    result = cursor.execute(mySql_insert_query, insert_tuple)
    connection.commit()
    print("Date Record inserted successfully")

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

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

You can get output like this after the execution of the above code.

Date Record inserted successfully
MySQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.