Python MySQL - Insert Multiple Rows in MySQL

What You'll Learn Here

Why insert multiple rows — efficiency and speed benefits
Single vs multiple — comparison of both approaches
SQL syntax — how to write multi-row INSERT statements
executemany() method — Python's tool for batch insertion
Batch insertion — handling large datasets in chunks
Error handling — dealing with failures in batch operations

Why Insert Multiple Rows?

In the previous tutorial, we learned how to insert a single row into a MySQL table. But what if you need to insert hundreds or thousands of rows at once?

💡 Key concept: Inserting multiple rows in one query is much faster than inserting them one by one. It reduces network traffic and database overhead significantly.

Think of it like sending a package versus a truckload. If you need to send 100 packages to the same place, you wouldn't make 100 separate trips. You'd load them all onto one truck and make one trip. The database works the same way!

Real-World Scenarios for Multiple Row Inserts

# Common scenarios where you need multiple row inserts

# 1. Data Migration
# Moving data from CSV files or other databases into MySQL

# 2. Bulk Imports
# Importing product catalogs, customer lists, or inventory data

# 3. Analytics
# Inserting large datasets for analysis and reporting

# 4. Data Sync
# Syncing data between different systems or databases

# 5. Initial Data Population
# Setting up a new database with initial data

# Example: Adding multiple students at once
students_to_add = [
    ("Rahul", "Sharma", 22, "rahul@email.com"),
    ("Priya", "Patel", 25, "priya@email.com"),
    ("Amit", "Singh", 24, "amit@email.com"),
    ("Sneha", "Reddy", 23, "sneha@email.com"),
    ("Vikram", "Kumar", 26, "vikram@email.com")
]

Why it matters:

Speed — 5-10 times faster than single row inserts
Network efficiency — fewer round trips to the database
Resource usage — less CPU and memory overhead
Transaction management — easier to manage as a single unit

Quick Check: Why is inserting multiple rows at once faster? (Answer: It reduces network round trips and database overhead)

Single Row vs Multiple Rows

Understanding the Difference

# ============================================================
# SINGLE ROW INSERT (Slow method)
# ============================================================

# This does 5 separate trips to the database
cursor.execute("INSERT INTO students (name, age) VALUES ('Rahul', 22)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Priya', 25)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Amit', 24)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Sneha', 23)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Vikram', 26)")
connection.commit()

# ⏱️ Takes about 0.5-1 second for 5 rows
# ⏱️ Takes about 5-10 seconds for 1000 rows

# ============================================================
# MULTIPLE ROW INSERT (Fast method)
# ============================================================

# This makes ONE trip to the database
cursor.executemany(
    "INSERT INTO students (name, age) VALUES (%s, %s)",
    [("Rahul", 22), ("Priya", 25), ("Amit", 24), ("Sneha", 23), ("Vikram", 26)]
)
connection.commit()

# ⏱️ Takes about 0.05-0.1 seconds for 5 rows
# ⏱️ Takes about 0.5-1 second for 1000 rows

# The difference becomes huge with more rows!

Performance comparison:

Single row — 5 round trips to the database
Multiple rows — 1 round trip to the database
5 rows — 10x faster with multiple rows
1000 rows — 10x faster with multiple rows
10,000 rows — even bigger difference!

Quick Check: How many round trips does multiple row insert make? (Answer: One round trip)

SQL Syntax for Multiple Rows

Understanding the Multi-Row INSERT Syntax

# ============================================================
# BASIC SYNTAX
# ============================================================

# Single row
INSERT INTO students (name, age) VALUES ('Rahul', 22);

# Multiple rows
INSERT INTO students (name, age) 
VALUES 
    ('Rahul', 22),
    ('Priya', 25),
    ('Amit', 24),
    ('Sneha', 23),
    ('Vikram', 26);

# ============================================================
# SYNTAX BREAKDOWN
# ============================================================

# Notice the structure:
# 1. INSERT INTO table_name (columns)
# 2. VALUES
# 3. Each row in parentheses: (value1, value2)
# 4. Rows separated by commas: ,
# 5. Last row has no comma after it

# ============================================================
# FULL EXAMPLE WITH ALL COLUMNS
# ============================================================

INSERT INTO students (first_name, last_name, age, email, joined_date)
VALUES 
    ('Rahul', 'Sharma', 22, 'rahul@email.com', '2024-01-15'),
    ('Priya', 'Patel', 25, 'priya@email.com', '2024-01-15'),
    ('Amit', 'Singh', 24, 'amit@email.com', '2024-01-16');

# You can insert any number of rows this way!

Key points about multi-row INSERT syntax:

Each row is inside parentheses ()
Rows are separated by commas ,
The last row doesn't have a comma after it
The semicolon ; ends the statement
MySQL can handle thousands of rows in one query

Quick Check: What separates multiple rows in a multi-row INSERT? (Answer: Commas)

The executemany() Method

Python's Tool for Batch Insertion

# ============================================================
# USING executemany() - The Right Way
# ============================================================

import mysql.connector

# Connect to MySQL
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)
cursor = connection.cursor()

# ============================================================
# EXAMPLE 1: Inserting Students
# ============================================================

query = "INSERT INTO students (first_name, last_name, age) VALUES (%s, %s, %s)"

# Data as a list of tuples
students_data = [
    ("Rahul", "Sharma", 22),
    ("Priya", "Patel", 25),
    ("Amit", "Singh", 24),
    ("Sneha", "Reddy", 23),
    ("Vikram", "Kumar", 26)
]

# Insert all students in one go
cursor.executemany(query, students_data)
connection.commit()

print(f"✅ {cursor.rowcount} students inserted successfully!")

# ============================================================
# EXAMPLE 2: Inserting Products
# ============================================================

product_query = """
    INSERT INTO products (product_name, price, stock_quantity)
    VALUES (%s, %s, %s)
"""

products = [
    ("Laptop", 999.99, 10),
    ("Mouse", 29.99, 50),
    ("Keyboard", 49.99, 30),
    ("Monitor", 299.99, 15)
]

cursor.executemany(product_query, products)
connection.commit()

print(f"✅ {cursor.rowcount} products inserted!")

# Close connections
cursor.close()
connection.close()

Important points about executemany():

Syntax: cursor.executemany(query, data_list)
Data format: List of tuples
Returns: Number of rows inserted
Efficiency: One round trip to the database
Security: Uses parameterized queries

Quick Check: What data format does executemany() expect? (Answer: A list of tuples)

Batch Insertion – Handling Large Datasets

Inserting Thousands of Rows Efficiently

# ============================================================
# BATCH INSERTION - For Large Datasets
# ============================================================

def batch_insert(cursor, connection, data, batch_size=500):
    """
    Insert data in batches to avoid memory issues.
    
    Args:
        cursor: MySQL cursor
        connection: MySQL connection
        data: List of tuples to insert
        batch_size: Number of rows per batch (default: 500)
    """
    query = """
        INSERT INTO students (first_name, last_name, age, email, joined_date)
        VALUES (%s, %s, %s, %s, %s)
    """
    
    total_rows = len(data)
    inserted = 0
    
    try:
        # Process in batches
        for i in range(0, total_rows, batch_size):
            batch = data[i:i + batch_size]
            cursor.executemany(query, batch)
            connection.commit()
            
            inserted += len(batch)
            print(f"✅ Batch {i//batch_size + 1}: Inserted {len(batch)} rows")
        
        print(f"🎉 Total: {inserted} rows inserted successfully!")
        return inserted
        
    except mysql.connector.Error as e:
        connection.rollback()
        print(f"❌ Error at batch: {e}")
        return inserted

# ============================================================
# GENERATING SAMPLE DATA
# ============================================================

# Generate 10,000 sample students
sample_data = []
for i in range(10000):
    sample_data.append((
        f"Student_{i}",
        f"LastName_{i}",
        18 + (i % 10),
        f"student_{i}@email.com",
        "2024-01-01"
    ))

print(f"📊 Generated {len(sample_data)} students")

# Insert in batches of 1000
batch_insert(cursor, connection, sample_data, batch_size=1000)

# Clean up
cursor.close()
connection.close()

Why batch insertion is important:

Memory efficient — processes data in chunks
Prevents timeouts — avoids long-running queries
Progress tracking — shows insertion progress
Error recovery — if one batch fails, others succeed
Optimal performance — 500-1000 rows per batch is ideal

Quick Check: What is the recommended batch size for large datasets? (Answer: 500-1000 rows per batch)

Performance Tips

Optimizing Your Inserts

# ============================================================
# PERFORMANCE TIPS FOR MULTIPLE ROW INSERTS
# ============================================================

# 1. Use executemany() instead of a loop
# ❌ BAD - One by one
for student in students:
    cursor.execute(query, student)
    connection.commit()

# ✅ GOOD - All at once
cursor.executemany(query, students)
connection.commit()

# 2. Commit after each batch, not after each row
# ❌ BAD - Committing each row
for student in students:
    cursor.execute(query, student)
    connection.commit()

# ✅ GOOD - Commit after batch
cursor.executemany(query, batch)
connection.commit()

# 3. Disable autocommit for bulk inserts
# ✅ GOOD - Manual commit
connection.autocommit = False
cursor.executemany(query, data)
connection.commit()

# 4. Use appropriate batch size
# ✅ GOOD - 500-1000 rows per batch
for batch in chunks(data, batch_size=500):
    cursor.executemany(query, batch)
    connection.commit()

# 5. Remove indexes before bulk insert (for very large datasets)
# ALTER TABLE students DROP INDEX index_name;
# Then re-create after insertion

Key performance tips:

Use executemany() — always for multiple rows
Batch commit — commit after each batch, not each row
Disable autocommit — speeds up bulk inserts
Optimal batch size — 500-1000 rows
Remove indexes — for very large datasets

Quick Check: When should you disable autocommit? (Answer: For bulk inserts to improve performance)

Error Handling

Dealing with Batch Insertion Errors

# ============================================================
# ERROR HANDLING FOR BATCH INSERTS
# ============================================================

def insert_with_error_handling(cursor, connection, data):
    """Insert multiple rows with comprehensive error handling"""
    
    query = """
        INSERT INTO students (first_name, last_name, age, email)
        VALUES (%s, %s, %s, %s)
    """
    
    successful_rows = 0
    failed_rows = []
    
    for idx, row in enumerate(data):
        try:
            cursor.execute(query, row)
            successful_rows += 1
            
            # Commit every 100 rows
            if successful_rows % 100 == 0:
                connection.commit()
                
        except mysql.connector.IntegrityError as e:
            # Duplicate key error
            connection.rollback()
            print(f"⚠️ Row {idx+1} skipped: Duplicate entry - {e}")
            failed_rows.append({"row": row, "error": "Duplicate"})
            
        except mysql.connector.DataError as e:
            # Data type mismatch
            connection.rollback()
            print(f"⚠️ Row {idx+1} skipped: Data error - {e}")
            failed_rows.append({"row": row, "error": "Data error"})
            
        except mysql.connector.Error as e:
            # Other database errors
            connection.rollback()
            print(f"❌ Row {idx+1} failed: {e}")
            failed_rows.append({"row": row, "error": str(e)})
    
    # Final commit for remaining rows
    connection.commit()
    
    print(f"✅ Inserted: {successful_rows} rows")
    print(f"❌ Failed: {len(failed_rows)} rows")
    
    return {
        "successful": successful_rows,
        "failed": len(failed_rows),
        "failed_details": failed_rows
    }

Common errors and how to handle them:

IntegrityError — duplicate entries, foreign key violations
DataError — wrong data type, invalid values
OperationalError — connection issues, timeouts
Use rollback() — to undo changes on error
Track failed rows — for retry or logging

Quick Check: What should you do when a batch insert fails? (Answer: Rollback and handle the error appropriately)

Real-World Example: CSV Import

Importing Data from CSV Files

# ============================================================
# COMPLETE CSV IMPORTER
# ============================================================

import csv
import mysql.connector
from mysql.connector import Error

class CSVImporter:
    """Import CSV data into MySQL database"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """Establish database connection"""
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            print("✅ Connected to MySQL")
            return True
        except Error as e:
            print(f"❌ Connection failed: {e}")
            return False
    
    def import_csv(self, csv_file, table_name, batch_size=500):
        """
        Import CSV data into MySQL table.
        
        Args:
            csv_file: Path to CSV file
            table_name: Target MySQL table
            batch_size: Rows per batch (default: 500)
        """
        try:
            # Read CSV file
            data = []
            with open(csv_file, 'r', encoding='utf-8') as file:
                reader = csv.DictReader(file)
                headers = reader.fieldnames
                
                for row in reader:
                    # Convert row to tuple in the right order
                    data.append(tuple(row[col] for col in headers))
            
            print(f"📄 Read {len(data)} rows from CSV")
            
            # Build INSERT query
            placeholders = ", ".join(["%s"] * len(headers))
            query = f"INSERT INTO {table_name} ({', '.join(headers)}) VALUES ({placeholders})"
            
            # Insert in batches
            total_inserted = 0
            for i in range(0, len(data), batch_size):
                batch = data[i:i + batch_size]
                
                try:
                    self.cursor.executemany(query, batch)
                    self.connection.commit()
                    
                    total_inserted += len(batch)
                    print(f"📦 Batch {i//batch_size + 1}: Inserted {len(batch)} rows")
                    
                except Error as e:
                    self.connection.rollback()
                    print(f"❌ Batch {i//batch_size + 1} failed: {e}")
                    continue
            
            print(f"🎉 Import complete! {total_inserted} rows inserted")
            return total_inserted
            
        except FileNotFoundError:
            print(f"❌ CSV file not found: {csv_file}")
            return 0
        except Exception as e:
            print(f"❌ Error: {e}")
            return 0
    
    def close(self):
        """Clean up connections"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# USAGE EXAMPLE
# ============================================================

if __name__ == "__main__":
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "secret",
        "database": "myapp_db"
    }
    
    importer = CSVImporter(db_config)
    if importer.connect():
        importer.import_csv("students.csv", "students")
        importer.close()

This real-world example shows:

Reading CSV files with Python's csv module
Building dynamic INSERT queries from CSV headers
Batch insertion for large CSV files
Proper error handling with rollback
Progress tracking for the user

Quick Check: What module is used to read CSV files? (Answer: The csv module)

Best Practices

Guidelines for Multiple Row Inserts

# ============================================================
# BEST PRACTICES SUMMARY
# ============================================================

print("=" * 60)
print("BEST PRACTICES FOR MULTIPLE ROW INSERTS")
print("=" * 60)

# 1. Always use executemany() for > 10 rows
print("\n1. ALWAYS USE EXECUTEMANY()")
print("   - Faster than execute() in a loop")
print("   - One round trip to the database")
print("   - 5-10x faster for bulk inserts")

# 2. Use batching for large datasets
print("\n2. USE BATCHING FOR LARGE DATASETS")
print("   - Insert in batches of 500-1000 rows")
print("   - Prevents memory issues")
print("   - Avoids timeout errors")

# 3. Commit after each batch
print("\n3. COMMIT AFTER EACH BATCH")
print("   - Not after each row")
print("   - Reduces disk I/O")
print("   - Makes inserts faster")

# 4. Use parameterized queries
print("\n4. USE PARAMETERIZED QUERIES")
print("   - Protects against SQL injection")
print("   - Uses %s placeholders")
print("   - Never use f-strings for SQL")

# 5. Handle errors gracefully
print("\n5. HANDLE ERRORS GRACEFULLY")
print("   - Use try/except blocks")
print("   - Rollback on errors")
print("   - Continue with next batch")

# 6. Monitor progress
print("\n6. MONITOR PROGRESS")
print("   - Show batch completion")
print("   - Track total rows inserted")
print("   - Log errors for review")

Summary of best practices:

Use executemany() — always for multiple rows
Batch insertion — 500-1000 rows per batch
Commit after batch — not after each row
Parameterized queries — security first
Error handling — rollback and continue
Progress tracking — keep users informed

Quick Check: What is the most important rule for multiple row inserts? (Answer: Always use executemany() for > 10 rows)

Try It Yourself

Experiment with inserting multiple rows in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL Insert Multiple Rows - Practice

print("=" * 40)
print("INSERT MULTIPLE ROWS - PRACTICE")
print("=" * 40)

# Simulated Database for learning
class SimulatedDB:
    students = []
    next_id = 1
    
    @classmethod
    def insert_many(cls, data):
        """Insert multiple students at once"""
        count = 0
        for student in data:
            # Check for duplicate email
            duplicate = False
            for existing in cls.students:
                if existing['email'] == student['email']:
                    print(f"⚠️ Duplicate email: {student['email']} (skipped)")
                    duplicate = True
                    break
            
            if not duplicate:
                cls.students.append({
                    'id': cls.next_id,
                    'first_name': student[0],
                    'last_name': student[1],
                    'age': student[2],
                    'email': student[3]
                })
                cls.next_id += 1
                count += 1
        
        print(f"✅ Inserted {count} students successfully!")
        return count
    
    @classmethod
    def list_all(cls):
        """Show all students"""
        if not cls.students:
            print("📭 No students yet!")
            return
        print("\n📋 All Students:")
        print("-" * 55)
        for s in cls.students:
            print(f"   ID: {s['id']:2} | {s['first_name']:10} {s['last_name']:10} | Age: {s['age']:2} | {s['email']}")
        print("-" * 55)
        print(f"   Total: {len(cls.students)} students")

print("\n📝 Inserting multiple students at once...")
print("-" * 40)

# Data to insert (5 students)
students_data = [
    ("Rahul", "Sharma", 22, "rahul@email.com"),
    ("Priya", "Patel", 25, "priya@email.com"),
    ("Amit", "Singh", 24, "amit@email.com"),
    ("Sneha", "Reddy", 23, "sneha@email.com"),
    ("Vikram", "Kumar", 26, "vikram@email.com")
]

SimulatedDB.insert_many(students_data)

print("\n📝 Inserting 3 more students...")
more_students = [
    ("Anjali", "Nair", 21, "anjali@email.com"),
    ("Ravi", "Desai", 27, "ravi@email.com"),
    ("Meera", "Iyer", 23, "meera@email.com")
]
SimulatedDB.insert_many(more_students)

print("\n🔄 Trying to insert a student with duplicate email...")
duplicate_data = [("Duplicate", "User", 30, "rahul@email.com")]
SimulatedDB.insert_many(duplicate_data)

# Show all students
SimulatedDB.list_all()

print("\n✅ Multiple row insertion is efficient and fast!")

🏆

You've Got It!

You now understand how to insert multiple rows into MySQL from Python. You know how to use executemany(), batch insertion, and handle errors effectively.

Quick Quiz

Test what you've learned:

1. Which method is used to insert multiple rows in Python's MySQL connector?

A) cursor.execute_many() B) cursor.executemany() C) cursor.insertmany() D) cursor.bulk_insert()

2. How much faster is batch insertion compared to single row insertion?

A) About the same speed B) 2-3 times faster C) 5-10 times faster D) 50-100 times faster

3. What is the recommended batch size for large datasets?

A) 10-50 rows B) 500-1000 rows C) 10,000-50,000 rows D) 1 million rows

4. What happens if one row fails in executemany()?

A) The entire batch fails B) Only that row fails, others succeed C) The database crashes D) The row is skipped automatically

5. What should you do to optimize performance for bulk inserts?

A) Commit after every row B) Use f-strings for SQL C) Use executemany() and commit after each batch D) Insert all rows in one giant query

Frequently Asked Questions

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

execute() runs a single query with one set of values. executemany() runs the same query multiple times with different values. It's much faster for inserting many rows because it reduces network overhead.

What's the maximum number of rows I can insert at once?

The limit is set by the max_allowed_packet setting in MySQL (default is 4MB-16MB). For practical purposes, insert in batches of 500-1000 rows to avoid memory and timeout issues.

Why should I use parameterized queries?

Parameterized queries protect against SQL injection attacks. They separate the SQL code from the data, making it impossible for malicious data to change the query structure.

What happens if I insert a duplicate email?

If you have a UNIQUE constraint on the email column, MySQL will raise an IntegrityError. You should catch this error, rollback the transaction, and handle it appropriately.

Can I use executemany() with ON DUPLICATE KEY UPDATE?

Yes! You can use INSERT ... ON DUPLICATE KEY UPDATE with executemany(). This will insert new rows and update existing ones if a duplicate key is found.

How can I track progress during a large insert?

You can track progress by counting rows as you insert them. Use cursor.rowcount after each batch, or maintain a counter variable to show percentage completion.

Where to Go From Here

Now that you know how to insert multiple rows efficiently, check out these related topics:

Previous: Insert Data in MySQL Next: Select Data from MySQL →

Python

Python: Insert Multiple Rows in MySQL

Why Insert Multiple Rows?

Real-World Scenarios for Multiple Row Inserts

Single Row vs Multiple Rows

Understanding the Difference

SQL Syntax for Multiple Rows

Understanding the Multi-Row INSERT Syntax

The executemany() Method

Python's Tool for Batch Insertion

Batch Insertion – Handling Large Datasets

Inserting Thousands of Rows Efficiently

Performance Tips

Optimizing Your Inserts

Error Handling

Dealing with Batch Insertion Errors

Real-World Example: CSV Import

Importing Data from CSV Files

Best Practices

Guidelines for Multiple Row Inserts

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

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

What's the maximum number of rows I can insert at once?

Why should I use parameterized queries?

What happens if I insert a duplicate email?

Can I use executemany() with ON DUPLICATE KEY UPDATE?

How can I track progress during a large insert?

Where to Go From Here

Insert Single Row

Select Data

WHERE Clause

Python

Python: Insert Multiple Rows in MySQL

Why Insert Multiple Rows?

Real-World Scenarios for Multiple Row Inserts

Single Row vs Multiple Rows

Understanding the Difference

SQL Syntax for Multiple Rows

Understanding the Multi-Row INSERT Syntax

The executemany() Method

Python's Tool for Batch Insertion

Batch Insertion – Handling Large Datasets

Inserting Thousands of Rows Efficiently

Performance Tips

Optimizing Your Inserts

Error Handling

Dealing with Batch Insertion Errors

Real-World Example: CSV Import

Importing Data from CSV Files

Best Practices

Guidelines for Multiple Row Inserts

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between execute() and executemany()? ▼

What's the maximum number of rows I can insert at once? ▼

Why should I use parameterized queries? ▼

What happens if I insert a duplicate email? ▼

Can I use executemany() with ON DUPLICATE KEY UPDATE? ▼

How can I track progress during a large insert? ▼

Where to Go From Here

Insert Single Row

Select Data

WHERE Clause

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

What's the maximum number of rows I can insert at once?

Why should I use parameterized queries?

What happens if I insert a duplicate email?

Can I use executemany() with ON DUPLICATE KEY UPDATE?

How can I track progress during a large insert?