Python MySQL Insert Data

📝 Add Data to Your Table!

Inserting data is like adding new rows to your spreadsheet. Let's add some students to our table!


cursor.execute("""
INSERT INTO students (name, age, email)
VALUES (%s, %s, %s)
""", ("John Doe", 20, "[email protected]"))

INSERT

Command

Safe

Method

Easy

To Do

What is Inserting Data?

Inserting data means:

➕ Adding new rows to your table
📝 Filling in information like names, ages, emails
💾 Saving data permanently in your database
📊 Building your database with real information

Add Your First Student

Simple Insert


import mysql.connector

# Connect to database
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpass',
    database='my_school'
)

cursor = connection.cursor()

# Add one student
cursor.execute("""
INSERT INTO students (name, age, email)
VALUES (%s, %s, %s)
""", ("John Doe", 20, "[email protected]"))

connection.commit()  # Save the changes!

print("✅ Student added!")
print(f"🆔 Student ID: {cursor.lastrowid}")

cursor.close()
connection.close()

💡 Important!

connection.commit() saves your changes. Without it, your data won't be saved!

The Safe Way (Always Use This!)

Safe Insert with Error Handling


import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='yourpass',
        database='my_school'
    )
    
    cursor = connection.cursor()
    
    # Safe way to insert data
    insert_query = "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)"
    student_data = ("Alice Smith", 19, "[email protected]")
    
    cursor.execute(insert_query, student_data)
    connection.commit()
    
    print("✅ Student added successfully!")
    
except mysql.connector.Error as error:
    print(f"❌ Error: {error}")
    
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

Why Use %s? (Very Important!)

❌

Wrong Way

Never do this:
f"INSERT INTO students VALUES ('{name}')"

Dangerous Can be hacked

✅

Right Way

Always do this:
"INSERT INTO students VALUES (%s)"

Safe Professional

Add Multiple Students

Insert Many Records


import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='yourpass',
        database='my_school'
    )
    
    cursor = connection.cursor()
    
    # Multiple students data
    students = [
        ("Emma Wilson", 18, "[email protected]"),
        ("Mike Brown", 21, "[email protected]"),
        ("Sarah Davis", 19, "[email protected]")
    ]
    
    insert_query = "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)"
    
    # Insert all students at once
    cursor.executemany(insert_query, students)
    connection.commit()
    
    print(f"✅ Added {cursor.rowcount} students!")
    
except mysql.connector.Error as error:
    print(f"❌ Error: {error}")
    
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

Insert Different Types of Data

Products Table Example


import mysql.connector
from datetime import date

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='yourpass',
        database='my_store'
    )
    
    cursor = connection.cursor()
    
    # Insert product with different data types
    cursor.execute("""
    INSERT INTO products (name, price, quantity, created_date)
    VALUES (%s, %s, %s, %s)
    """, (
        "Laptop",           # Text (VARCHAR)
        999.99,            # Money (DECIMAL)
        5,                 # Number (INT)
        date.today()       # Date (DATE)
    ))
    
    connection.commit()
    print("✅ Product added!")
    
except mysql.connector.Error as error:
    print(f"❌ Error: {error}")
    
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

Make It Super Easy

Easy Student Adder


import mysql.connector

def add_student(name, age, email):
    """Simple function to add a student"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='yourpass',
            database='my_school'
        )
        
        cursor = connection.cursor()
        
        cursor.execute("""
        INSERT INTO students (name, age, email)
        VALUES (%s, %s, %s)
        """, (name, age, email))
        
        connection.commit()
        
        print(f"✅ Added {name} to database!")
        return True
        
    except mysql.connector.Error as error:
        print(f"❌ Error adding {name}: {error}")
        return False
        
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# Use it like this:
add_student("Tom Wilson", 22, "[email protected]")
add_student("Lisa Garcia", 20, "[email protected]")

What If Something Goes Wrong?

Handle Common Errors


import mysql.connector

def safe_add_student(name, age, email):
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='yourpass',
            database='my_school'
        )
        
        cursor = connection.cursor()
        
        cursor.execute("""
        INSERT INTO students (name, age, email)
        VALUES (%s, %s, %s)
        """, (name, age, email))
        
        connection.commit()
        print(f"✅ {name} added successfully!")
        
    except mysql.connector.IntegrityError:
        print(f"❌ Email '{email}' already exists!")
        
    except mysql.connector.Error as error:
        print(f"❌ Database error: {error}")
        
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# Test it
safe_add_student("New Student", 20, "[email protected]")
safe_add_student("Another Student", 21, "[email protected]")  # Same email - will show error

Simple Tips

🛡️ Always Use %s

Never put data directly in the query string

💾 Don't Forget commit()

Your data won't save without it!

🔍 Handle Errors

Use try/except to catch problems

🔒 Close Connections

Always close when you're done

🎯 Quick Practice

Try This Yourself


# Challenge: Add these students to your database
students_to_add = [
    ("Alex Johnson", 19, "[email protected]"),
    ("Maria Rodriguez", 20, "[email protected]"),
    ("David Kim", 18, "[email protected]")
]

# Your code here:
# 1. Connect to database
# 2. Insert each student
# 3. Print success message
# 4. Close connection

print("Try adding these students yourself! 🎓")