Python MySQL - Insert Data in MySQL

📖 What is INSERT in MySQL?

Imagine you've built a beautiful house. You've set up the rooms (that's your database), you've placed the furniture (that's your tables), and now it's time to move people in.

In the database world, INSERT is exactly that — it's how you add data to your tables. It's the command that says, "Hey MySQL, here's some information, please store it for me."

📝 Think of it like filling out a form.

You know when you fill out a form online? You enter your name, email, address, and click "Submit." The website then inserts that data into their database.

That's exactly what INSERT does! It takes the data you provide and adds it as a new row in your table.

🐍 Python Code ➡️ 📊 MySQL Table
(Your data travels from Python to the database)

Why is INSERT so important?

It's how your application stores user data — signups, orders, posts, comments
It's how data gets into your database — without INSERT, your tables would always be empty
It's the foundation of CRUD — Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE)

💡 Fun fact: In a typical web application, INSERT operations happen all the time. Every time someone signs up, posts a comment, or places an order — that's an INSERT!

🔧 INSERT Syntax Explained

Before we write any Python code, let's understand the SQL syntax for INSERT. It's actually quite simple!

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Let me break this down piece by piece:

INSERT INTO – "Hey MySQL, I want to add new data"

table_name – "Which table should I add it to?"

(column1, column2, ...) – "Here are the columns I want to fill"

VALUES (value1, value2, ...) – "And here are the values for those columns"

Here's a real example:

INSERT INTO students (first_name, last_name, age, email)
VALUES ('Rahul', 'Sharma', 22, 'rahul@email.com');

This adds a new student named Rahul Sharma, age 22, with email rahul@email.com.

💡 Important: The order of columns and values must match! The first value goes into the first column, second value into the second column, and so on.

Three Ways to Write INSERT

Method 1: Specify columns (Recommended)

INSERT INTO students (first_name, last_name, age) 
VALUES ('Priya', 'Patel', 25);

✅ Best practice: Always specify column names. It's clearer and more maintainable.

Method 2: Insert into all columns

INSERT INTO students 
VALUES (NULL, 'Amit', 'Singh', 24, 'amit@email.com', '2024-01-15');

⚠️ You must provide values for ALL columns in order. NULL is used for AUTO_INCREMENT columns.

Method 3: Insert multiple rows (Efficient)

INSERT INTO students (first_name, last_name, age) 
VALUES 
    ('Sneha', 'Reddy', 23),
    ('Vikram', 'Kumar', 26),
    ('Anjali', 'Nair', 21);

🚀 Efficient: Insert multiple rows in a single query. Much faster than inserting one by one!

📌 Remember: When you use AUTO_INCREMENT columns (like student_id), you don't need to provide a value. MySQL will generate it automatically. Just use NULL or skip the column entirely.

✏️ Insert a Single Row

Let's start with the simplest case: adding one student to our table.

Before we begin: Make sure you have the students table from the previous tutorial. If not, here's a quick reminder of what it looks like:

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    joined_date DATE
);

Now, let's insert a student:

1 Connect to the database

import mysql.connector

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

2 Write the INSERT query

insert_query = """
INSERT INTO students (first_name, last_name, age, email, joined_date)
VALUES ('Rahul', 'Sharma', 22, 'rahul@email.com', '2024-01-15')
"""

3 Execute and commit

cursor.execute(insert_query)
connection.commit()  # Save changes to the database

print("✅ Student added successfully!")

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

🎉 That's it! You just inserted your first record into the database. Notice that we didn't specify student_id – MySQL handled it automatically!

💡 Pro tip: Always use connection.commit() after INSERT, UPDATE, or DELETE. Without it, your changes won't be saved to the database. This is called a transaction, and we'll learn more about it later.

📋 Insert Multiple Rows

What if you have a list of students to add? You could insert them one by one, but that's slow. The better way is to insert all of them in one query.

🚗 Think of it like a carpool vs driving alone.

If you have 5 people going to the same place, it's faster to put them all in one car than to take 5 separate cars.

Same with INSERT! Inserting multiple rows in one query is much faster than inserting them one by one.

Here's how to insert multiple students:

insert_multiple_query = """
INSERT INTO students (first_name, last_name, age, email, joined_date)
VALUES 
    ('Priya', 'Patel', 25, 'priya@email.com', '2024-01-15'),
    ('Amit', 'Singh', 24, 'amit@email.com', '2024-01-16'),
    ('Sneha', 'Reddy', 23, 'sneha@email.com', '2024-01-16'),
    ('Vikram', 'Kumar', 26, 'vikram@email.com', '2024-01-17'),
    ('Anjali', 'Nair', 21, 'anjali@email.com', '2024-01-17')
"""

cursor.execute(insert_multiple_query)
connection.commit()

print("✅ 5 students added successfully!")

Notice the structure:

Each row is inside parentheses ()
Rows are separated by commas ,
The last row doesn't have a comma after it

⚡ Performance tip: Inserting 100 rows in one query is about 5-10 times faster than inserting them individually. Always batch your inserts when possible!

Inserting Multiple Rows from Python Lists

In real applications, you often have data in Python lists or dictionaries. Here's how to insert them efficiently:

# List of students as dictionaries
students = [
    {"first_name": "Raj", "last_name": "Kumar", "age": 22, "email": "raj@email.com", "joined_date": "2024-01-18"},
    {"first_name": "Meera", "last_name": "Iyer", "age": 23, "email": "meera@email.com", "joined_date": "2024-01-18"},
    # ... more students
]

# Build the query dynamically
values_list = []
for s in students:
    values_list.append(f"('{s['first_name']}', '{s['last_name']}', {s['age']}, '{s['email']}', '{s['joined_date']}')")

query = f"""
INSERT INTO students (first_name, last_name, age, email, joined_date)
VALUES {', '.join(values_list)}
"""

cursor.execute(query)
connection.commit()
print(f"✅ {len(students)} students added!")

⚠️ Security warning: The above example uses f-strings to build the query. This is NOT SAFE for production because it's vulnerable to SQL injection. We'll learn the proper (safe) way in the next section using parameterized queries.

💾 COMMIT – Saving Your Changes

This is something that confuses many beginners. Let me explain it with a simple analogy.

📝 Think of it like writing an exam.

You write all your answers on the paper. But the paper isn't officially submitted until you hand it over to the invigilator.

In MySQL:
INSERT, UPDATE, DELETE = writing your answers
COMMIT = submitting your paper

❌ Without COMMIT

Changes exist only in memory
Other users can't see them
If your program crashes, changes are lost

✅ With COMMIT

Changes are saved to disk
Other users can see them
Changes are permanent (even after crash)

# Always commit your changes!
cursor.execute("INSERT INTO students ...")
connection.commit()  # Without this, nothing is saved!

# If something goes wrong, rollback
try:
    cursor.execute("INSERT INTO students ...")
    connection.commit()
except Exception as e:
    connection.rollback()  # Undo the changes
    print("Error, changes rolled back")

💡 Remember: connection.commit() is like hitting "Save" in a document. Without it, all your hard work disappears when you close the connection!

🔒 Parameterized Queries – The Right Way

This is extremely important for security. Let me explain why.

🚨 SQL Injection Alert!

Imagine this: Someone enters "Robert'; DROP TABLE students; --" as their name...

If you build queries with f-strings, that could delete your entire table!

Parameterized queries protect you from this. They separate the SQL code from the data.

❌ UNSAFE (Don't do this!)

name = "Robert"
# This is vulnerable to SQL injection!
cursor.execute(f"INSERT INTO students (first_name) VALUES ('{name}')")

✅ SAFE (Always do this!)

name = "Robert"
# Safe from SQL injection!
cursor.execute("INSERT INTO students (first_name) VALUES (%s)", (name,))

How parameterized queries work:

1 Placeholders

Use %s as a placeholder in your SQL query.

2 Pass values as a tuple

Pass the actual values as a second argument to execute().

3 MySQL handles escaping

MySQL automatically escapes special characters. It's impossible to inject SQL through these placeholders.

Examples of Parameterized Queries

# Example 1: Insert a single row
query = "INSERT INTO students (first_name, last_name, age) VALUES (%s, %s, %s)"
values = ("Rahul", "Sharma", 22)
cursor.execute(query, values)

# Example 2: Insert multiple rows with executemany()
query = "INSERT INTO students (first_name, last_name, age) VALUES (%s, %s, %s)"
students = [
    ("Priya", "Patel", 25),
    ("Amit", "Singh", 24),
    ("Sneha", "Reddy", 23)
]
cursor.executemany(query, students)  # Note: executemany, not execute!
connection.commit()
print(f"✅ {len(students)} students inserted")

🔒 Golden rule: NEVER use f-strings or string concatenation to build SQL queries with user input. ALWAYS use parameterized queries. It's not optional – it's a security necessity!

🔢 Getting the Last Insert ID

Often, after inserting a new record, you need to know what ID was assigned. For example, after a user signs up, you might want to use their new user ID for something else.

MySQL gives you a simple way to get this: lastrowid.

query = "INSERT INTO students (first_name, last_name) VALUES (%s, %s)"
values = ("Arjun", "Reddy")

cursor.execute(query, values)
connection.commit()

# Get the ID of the newly inserted row
new_id = cursor.lastrowid
print(f"✅ Student added with ID: {new_id}")

💡 When to use lastrowid:

After a user signs up → use their new ID to create a profile
After a customer places an order → use the order ID to show a confirmation
After a product is added → use the product ID to upload images

⚠️ Error Handling

Things can go wrong when inserting data. Here are the most common errors and how to handle them:

Duplicate Entry

Error: Duplicate entry 'rahul@email.com' for key 'email'
You tried to insert a duplicate value in a UNIQUE column.

Data Type Mismatch

Error: Incorrect integer value: 'abc' for column 'age'
You tried to insert text into a number column.

NOT NULL Violation

Error: Column 'first_name' cannot be null
You tried to insert NULL into a NOT NULL column.

How to handle errors properly:

from mysql.connector import Error

def insert_student(first_name, last_name, age, email):
    try:
        query = """
            INSERT INTO students (first_name, last_name, age, email)
            VALUES (%s, %s, %s, %s)
        """
        values = (first_name, last_name, age, email)
        
        cursor.execute(query, values)
        connection.commit()
        
        print(f"✅ Student {first_name} {last_name} added!")
        return cursor.lastrowid
        
    except mysql.connector.IntegrityError as e:
        if "Duplicate entry" in str(e):
            print("❌ This email already exists!")
        else:
            print("❌ Integrity error:", e)
        connection.rollback()
        return None
        
    except mysql.connector.DataError as e:
        print("❌ Data type error:", e)
        connection.rollback()
        return None
        
    except mysql.connector.Error as e:
        print("❌ Database error:", e)
        connection.rollback()
        return None
    
    finally:
        # Clean up
        pass

# Test it
insert_student("Rahul", "Sharma", 22, "rahul@email.com")  # Works
insert_student("Rahul", "Sharma", 22, "rahul@email.com")  # Fails - duplicate email

🛡️ Always use rollback(): When an error occurs, use connection.rollback() to undo any partial changes. This prevents your database from being left in an inconsistent state.

💼 Real-World Example: User Signup

Let's build something practical – a user signup system. This is a common real-world scenario:

A user fills out a signup form
We validate their input
We insert their data into the database
We check if the email already exists
We send a welcome message

import mysql.connector
from mysql.connector import Error

class UserSignup:
    """Handle user signup with MySQL"""
    
    def __init__(self, db_config):
        self.config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """Establish connection to MySQL"""
        try:
            self.connection = mysql.connector.connect(**self.config)
            self.cursor = self.connection.cursor()
            return True
        except Error as e:
            print(f"Connection error: {e}")
            return False
    
    def email_exists(self, email):
        """Check if email already exists in the database"""
        query = "SELECT COUNT(*) FROM students WHERE email = %s"
        self.cursor.execute(query, (email,))
        count = self.cursor.fetchone()[0]
        return count > 0
    
    def signup(self, first_name, last_name, age, email):
        """Register a new user"""
        
        # Validate input
        if not first_name or not last_name:
            return {"success": False, "message": "Name is required"}
        
        if not email or "@" not in email:
            return {"success": False, "message": "Valid email is required"}
        
        try:
            # Check if email exists
            if self.email_exists(email):
                return {"success": False, "message": "Email already registered"}
            
            # Insert the user
            query = """
                INSERT INTO students (first_name, last_name, age, email, joined_date)
                VALUES (%s, %s, %s, %s, CURDATE())
            """
            values = (first_name, last_name, age, email)
            
            self.cursor.execute(query, values)
            self.connection.commit()
            
            new_id = self.cursor.lastrowid
            
            # Send welcome email (simulated)
            self.send_welcome_email(email, first_name)
            
            return {
                "success": True,
                "message": "Welcome!",
                "user_id": new_id
            }
            
        except Error as e:
            self.connection.rollback()
            return {"success": False, "message": f"Signup failed: {e}"}
    
    def send_welcome_email(self, email, name):
        """Simulate sending a welcome email"""
        print(f"📧 Welcome email sent to {email} for {name}")
    
    def close(self):
        """Clean up connections"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()


# ============================================================
# DEMONSTRATION
# ============================================================

if __name__ == "__main__":
    # Configuration
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "secret",
        "database": "myapp_db"
    }
    
    # Create signup handler
    signup = UserSignup(db_config)
    
    if not signup.connect():
        exit()
    
    # Test signup
    print("\n📝 Testing User Signup:")
    print("-" * 40)
    
    # Valid signup
    result = signup.signup("Priya", "Patel", 25, "priya@email.com")
    print(f"Result: {result}")
    
    # Duplicate email
    result = signup.signup("Rahul", "Sharma", 22, "priya@email.com")
    print(f"Result: {result}")
    
    # Invalid email
    result = signup.signup("Amit", "Singh", 24, "invalid")
    print(f"Result: {result}")
    
    signup.close()

🎯 What we built: A complete user signup system with:

Input validation
Duplicate email checking
Secure parameterized queries
Error handling
Welcome email simulation
Proper cleanup

✅ Best Practices for INSERT

1 Always use parameterized queries

NEVER use f-strings or string concatenation for SQL. ALWAYS use %s placeholders. This is non-negotiable for security.

2 Always commit your changes

Use connection.commit() after every INSERT, UPDATE, or DELETE. Without it, your changes aren't saved.

3 Use executemany for multiple rows

For inserting many rows, use cursor.executemany(). It's much faster than inserting one by one.

4 Handle errors properly

Use try/except to catch errors. Use rollback() when errors occur to keep data consistent.

5 Validate data before inserting

Check email formats, required fields, and data types before sending to the database. It's better to catch errors early.

6 Use lastrowid when you need the new ID

After inserting, use cursor.lastrowid to get the auto-generated ID of the new record.

📌 Summary: The most important rule is security. Always use parameterized queries. The second most important rule is reliability. Always use commit, rollback, and error handling.

🎮 Try It Yourself

Play with the code below. Click "Run" to insert students into our simulated database!

Loading... 0%

Python Code

# ✏️ Insert Data - Practice

print("=" * 45)
print("INSERT DATA - PRACTICE")
print("=" * 45)

# Simulated Database for learning
class SimulatedDB:
    students = []
    next_id = 1
    
    @classmethod
    def insert(cls, first_name, last_name, age, email):
        """Insert a new student"""
        # Check for duplicate email
        for student in cls.students:
            if student['email'] == email:
                print(f"❌ Email '{email}' already exists!")
                return None
        
        # Create new student
        student = {
            'id': cls.next_id,
            'first_name': first_name,
            'last_name': last_name,
            'age': age,
            'email': email
        }
        cls.students.append(student)
        cls.next_id += 1
        print(f"✅ Added: {first_name} {last_name} (ID: {student['id']})")
        return student['id']
    
    @classmethod
    def list_all(cls):
        """Show all students"""
        if not cls.students:
            print("📭 No students yet!")
            return
        print("\n📋 All Students:")
        print("-" * 50)
        for s in cls.students:
            print(f"   ID: {s['id']} | {s['first_name']} {s['last_name']} | Age: {s['age']} | {s['email']}")
        print("-" * 50)
        print(f"   Total: {len(cls.students)} students")

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

# Insert single student
SimulatedDB.insert("Rahul", "Sharma", 22, "rahul@email.com")
SimulatedDB.insert("Priya", "Patel", 25, "priya@email.com")

# Insert multiple students
print("\n📝 Inserting 3 more students...")
students_to_add = [
    ("Amit", "Singh", 24, "amit@email.com"),
    ("Sneha", "Reddy", 23, "sneha@email.com"),
    ("Vikram", "Kumar", 26, "vikram@email.com")
]

for first, last, age, email in students_to_add:
    SimulatedDB.insert(first, last, age, email)

# Try duplicate email
print("\n🔄 Trying to insert duplicate email...")
SimulatedDB.insert("Duplicate", "User", 30, "rahul@email.com")

# Show all students
SimulatedDB.list_all()

print("\n✅ Done! Inserting data is fun!")

=============================================
INSERT DATA - PRACTICE
=============================================

📝 Inserting students...
----------------------------------------
✅ Added: Rahul Sharma (ID: 1)
✅ Added: Priya Patel (ID: 2)

📝 Inserting 3 more students...
✅ Added: Amit Singh (ID: 3)
✅ Added: Sneha Reddy (ID: 4)
✅ Added: Vikram Kumar (ID: 5)

🔄 Trying to insert duplicate email...
❌ Email 'rahul@email.com' already exists!

📋 All Students:
--------------------------------------------------
   ID: 1 | Rahul Sharma | Age: 22 | rahul@email.com
   ID: 2 | Priya Patel | Age: 25 | priya@email.com
   ID: 3 | Amit Singh | Age: 24 | amit@email.com
   ID: 4 | Sneha Reddy | Age: 23 | sneha@email.com
   ID: 5 | Vikram Kumar | Age: 26 | vikram@email.com
--------------------------------------------------
   Total: 5 students

✅ Done! Inserting data is fun!

🏆

You're Crushing It!

You now know how to insert data into MySQL from Python. You understand parameterized queries, error handling, and real-world use cases.

🧠 Quick Quiz

Test your INSERT knowledge!

1️⃣ What SQL command adds new data to a table?

A) ADD INTO table B) INSERT INTO table C) CREATE INTO table

2️⃣ What does connection.commit() do?

A) Deletes all data B) Saves changes to the database permanently C) Undoes all changes

3️⃣ Why should you use parameterized queries?

A) To make code shorter B) To prevent SQL injection attacks C) To make it run faster

4️⃣ How do you get the ID of the newly inserted row?

A) cursor.get_id() B) cursor.lastrowid C) connection.last_insert_id()

5️⃣ Which method is fastest for inserting many rows?

A) Inserting one row at a time in a loop B) Using executemany() with multiple rows C) They are all equally fast

❓ Frequently Asked Questions

🤔 Why do I need to commit after INSERT?

MySQL uses transactions by default. Your changes are stored temporarily until you commit them.

Why? It lets you:

Group multiple changes together
Undo (rollback) if something goes wrong
Keep the database in a consistent state

Without commit(), your data disappears when the connection closes!

🤔 What's 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.

Example: Inserting 1000 rows with executemany() takes about 0.5 seconds. Doing it with execute() in a loop takes about 5 seconds.

🤔 What happens if I insert a duplicate email?

If you have a UNIQUE constraint on the email column (which you should!), MySQL will raise an IntegrityError with the message "Duplicate entry."

How to handle it:

Catch the IntegrityError exception
Check if the error is about duplicate email
Show a friendly message to the user: "This email is already registered"
Use rollback() to undo the failed insert

🤔 Can I insert data without specifying all columns?

Yes! You only need to specify the columns you want to fill. MySQL will use:

DEFAULT values (if defined)
NULL (if the column allows it)
AUTO_INCREMENT values (automatically generated)

Example: INSERT INTO students (first_name, last_name) VALUES ('Rahul', 'Sharma');
This will insert a student with only first and last name. All other columns will get their default values.

🤔 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).

Practical advice:

Insert in batches of 100-1000 rows
Don't try to insert 100,000 rows in one query
If you have many rows, batch them and commit every 1000 rows

Example: executemany() can handle large batches, but it's better to split into smaller batches for memory and performance reasons.

⬅️ Previous: Create Table Next: Insert Multiple Rows →

Python

✏️ Python: Insert Data in MySQL