Python MySQL – Insert Data Into a Table

Summary: in this tutorial, you will learn how to insert data into a table using MySQL Connector/Python API.

This tutorial picks up where the Querying Data from a Table in Python tutorial left off.

To insert new rows into a MySQL table, you follow these steps:

• First, connect to the MySQL database server by creating a new MySQLConnection object.
• Second, create a MySQLCursor object from the MySQLConnection object.
• Third, execute the INSERT statement to insert data into the table.
• Finally, close the database connection.

MySQL Connector/Python provides an API that allows you to insert one or multiple rows into a table at a time. Let’s examine each method in more detail.

Insert one row into a table

The following program defines a function called insert_book() that inserts a new book into the books table:

from mysql.connector import MySQLConnection, Error
from config import read_config

def insert_book(title, isbn):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"

    args = (title, isbn)
    book_id = None
    try:
        config = read_config()
        with MySQLConnection(**config) as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, args)
                book_id =  cursor.lastrowid
            conn.commit()
        return book_id
    except Error as error:
        print(error)

if __name__ == '__main__':
    insert_book('A Sudden Light', '9781439187036')Code language: Python (python)

How it works.

First, import the necessary modules including the MySQLConnection and Error classes from the mysql.connector module, and read_config is a function imported from a custom module called config:

from mysql.connector import MySQLConnection, Error
from config import read_configCode language: JavaScript (javascript)

Second, define a function insert_book() that accepts two arguments: title and ISBN and prepare an SQL query to insert a new row into the books table:

def insert_book(title, isbn):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"
    args = (title, isbn)Code language: JavaScript (javascript)

Third, use the with statement to open a database connection (MySQLConnection) and create a cursor (cursor). We use the **config syntax to pass keyword arguments from the config dictionary:

    try:
        config = read_config()
        with MySQLConnection(**config) as conn:
            with conn.cursor() as cursor:
Code language: JavaScript (javascript)

Fourth, execute the query using the execute() method of the cursor with the provided arguments (args) and return the inserted id of the row:

                cursor.execute(query, args)
                book_id =  cursor.lastrowid

Sixth, commit the changes to the database using the commit() method:

            conn.commit()Code language: CSS (css)

Seventh, show the error message if it occurs during the execution:

    except Error as error:
        print(error)Code language: JavaScript (javascript)

Note that the with statement automatically takes care of closing the cursor and the database connection, even if an exception occurs.

Finally, call the insert_book() function to insert a new book into the books table:

if __name__ == '__main__':
    insert_book('A Sudden Light', '9781439187036')
Code language: JavaScript (javascript)

Insert multiple rows into a table

The following INSERT statement allows you to insert multiple rows into the books table:

INSERT INTO books(title,isbn)
VALUES('Harry Potter And The Order Of The Phoenix', '9780439358071'),
       ('Gone with the Wind', '9780446675536'),
       ('Pride and Prejudice (Modern Library Classics)', '9780679783268');Code language: SQL (Structured Query Language) (sql)

To insert multiple rows into a table from Python, you use the  executemany() method of the MySQLCursor object.

from mysql.connector import MySQLConnection, Error
from config import read_config

def insert_books(books):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"
    try:
        config = read_config()
        with MySQLConnection(**config) as conn:
            with conn.cursor() as cursor:
                cursor.executemany(query, books)
            conn.commit()
    except Error as error:
        print(error)

if __name__ == '__main__':
    books = [('Harry Potter And The Order Of The Phoenix', '9780439358071'),
             ('Gone with the Wind', '9780446675536'),
             ('Pride and Prejudice (Modern Library Classics)', '9780679783268')]
    insert_books(books)
Code language: Python (python)

The logic in this example is similar to the logic in the first example. However, instead of calling the  execute() method, we call executemany() method.

To insert multiple rows into the books table, we pass a list of tuples, each containing the title and ISBN of the book to the insert_books() function.

By calling the executemany() method of the MySQLCursor object, the MySQL Connector/Python translates the INSERT statement into the one that contains multiple lists of values.

Summary

• Use the execute() method to execute an INSERT statement that inserts one row into a table.
• Use the executeMany() method to execute an INSERT statement that inserts multiple rows into a table.