Python MySQL Insert Record: Single, Parameterized & Bulk Insertion

Interactive Google Colab Practice Notebook

Practice inserting single records, parameterized queries, bulk inserts using executemany(), and tracking auto-increment IDs via lastrowid directly inside Google Colab.

View on GitHub

We will use SQL INSERT to add records to our database student table using mysql.connector.

We defined my_cursor and my_connect in our database connection guide. Remember that data-modifying statements require an explicit commit() to permanently save changes unless autocommit is enabled on your connection.

my_cursor.execute("INSERT INTO `my_tutorial`.`student` (`id`, `name`, `class`, `mark`, `gender`) \
                  VALUES ('36', 'King', 'Five', '45', 'male')")
print("Rows Added =", my_cursor.rowcount)
my_connect.commit() # Commit transaction
my_connect.close()

Output:

Rows Added = 1

We used rowcount to verify the number of rows affected by the insert query.

Handling Errors During Insertion

If an error occurs (such as an invalid column name), MySQL returns an error message. We can catch and display database errors using try-except exception blocks.

my_cursor = my_connect.cursor() # my_connect is the active connection
try:
    my_cursor.execute("INSERT INTO `my_tutorial`.`student` (`id`, `name`, `class1`, `mark`, `gender`) \
                  VALUES ('36', 'King', 'Five', '45', 'male')")
    print("Rows Added =", my_cursor.rowcount)
    my_connect.commit()
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output:

1054 (42S22): Unknown column 'class1' in 'field list'

Using Parameterized Queries for Security

Placeholders (%s) should be used inside the SQL string while passing variables as a separate tuple. This standard practice prevents SQL injection attacks when handling untrusted user input.

my_cursor = my_connect.cursor()
try:
    query = "INSERT INTO `my_tutorial`.`student` (`id`, `name`, `class`, `mark`, `gender`) \
             VALUES (%s, %s, %s, %s, %s)"
    my_data = (36, 'King', 'Five', 45, 'male')
    
    my_cursor.execute(query, my_data)
    
    print("Rows Added =", my_cursor.rowcount)
    my_connect.commit()
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Adding Multiple Records Using executemany()

When inserting multiple records in bulk, passing a list of tuples to executemany() is significantly faster and cleaner than running single queries in a loop.

my_cursor = my_connect.cursor()
try:
    query = "INSERT INTO `my_tutorial`.`student` (`id`, `name`, `class`, `mark`, `gender`) \
             VALUES (%s, %s, %s, %s, %s)"
    my_data = [
        (36, 'King', 'Five', 45, 'male'),
        (37, 'Queen', 'Four', 44, 'female'),
        (38, 'Jack', 'Three', 42, 'male')
    ]
    
    my_cursor.executemany(query, my_data)
    
    print("Rows Added =", my_cursor.rowcount)
    my_connect.commit()
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output:

Rows Added = 3

Getting Record ID via lastrowid

When the table ID column is configured as AUTO_INCREMENT, MySQL automatically assigns a unique identifier to each new row.

You can retrieve the auto-generated primary key immediately after insertion using the cursor's lastrowid attribute.

my_cursor = my_connect.cursor()
try:
    query = "INSERT INTO `my_tutorial`.`student` (`name`, `class`, `mark`, `gender`) \
             VALUES (%s, %s, %s, %s)"
    my_data = ('King', 'Five', 45, 'male')
                
    my_cursor.execute(query, my_data)
    print("Rows Added =", my_cursor.rowcount)
    print("last row id =", my_cursor.lastrowid)
    my_connect.commit()
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output:

Rows Added = 1
last row id = 36

Frequently Asked Questions & Common Errors

If transaction autocommit is disabled on your connection, you must explicitly call my_connect.commit() after executing an INSERT, UPDATE, or DELETE statement to permanently write the changes to the database.

execute() runs a single SQL statement with one parameter set. executemany() executes the same SQL template across a sequence of multiple parameter tuples or dictionaries, minimizing round-trips to the database server and boosting bulk insertion performance.

lastrowid relies on the table having an AUTO_INCREMENT primary key column. If the table lacks an auto-increment field, or if you performed a bulk insert via executemany() (where behavior varies across DBAPI drivers), lastrowid may not return the expected generated key.

Always use parameterized queries by passing values separately as tuples or dictionaries to cursor.execute(), rather than string-formatting or concatenating variables directly into your SQL query string.

Python MySQL Insert Record: Single, Parameterized & Bulk Insertion

Interactive Google Colab Practice Notebook

Handling Errors During Insertion

Using Parameterized Queries for Security

Adding Multiple Records Using executemany()

Getting Record ID via lastrowid

Frequently Asked Questions & Common Errors

Related Tutorials & Next Steps

Subhendu Mohapatra

Python MySQL Insert Record: Single, Parameterized & Bulk Insertion

Interactive Google Colab Practice Notebook

Handling Errors During Insertion

Using Parameterized Queries for Security

Adding Multiple Records Using executemany()

Getting Record ID via lastrowid

Frequently Asked Questions & Common Errors

1. Why are my inserted rows missing after executing execute()?

2. What is the difference between execute() and executemany()?

3. Why does lastrowid return 0 or None after an insert?

4. How do I prevent SQL injection during inserts?

Related Tutorials & Next Steps

Subhendu Mohapatra