➕ Inserting Data — Adding Records to Your Database

Learn how to add data to tables efficiently and safely.

🎯 The INSERT Statement

INSERT adds new rows to a table:

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

# Basic INSERT
cursor.execute("""
    INSERT INTO users (name, age, email)
    VALUES ('Alice', 30, 'alice@example.com')
""")
conn.commit()

💡 Different INSERT Patterns

Single Row Insert

cursor.execute("""
    INSERT INTO users (name, age, email)
    VALUES ('Bob', 25, 'bob@example.com')
""")
conn.commit()

Multiple Rows (Sequential)

rows = [
    ('Alice', 30, 'alice@example.com'),
    ('Bob', 25, 'bob@example.com'),
    ('Carol', 28, 'carol@example.com')
]

for name, age, email in rows:
    cursor.execute("""
        INSERT INTO users (name, age, email)
        VALUES (?, ?, ?)
    """, (name, age, email))

conn.commit()

Multiple Rows (Batch Insert - Much Faster!)

rows = [
    ('Alice', 30, 'alice@example.com'),
    ('Bob', 25, 'bob@example.com'),
    ('Carol', 28, 'carol@example.com')
]

# executemany is 10-100x faster
cursor.executemany("""
    INSERT INTO users (name, age, email)
    VALUES (?, ?, ?)
""", rows)
conn.commit()

🔐 Safe Inserts with Placeholders

Never use string concatenation!

# ❌ BAD - SQL Injection vulnerability!
name = "Alice'; DROP TABLE users; --"
cursor.execute(f"INSERT INTO users (name) VALUES ('{name}')")

# ✅ GOOD - Safe with placeholders
cursor.execute("INSERT INTO users (name) VALUES (?)", (name,))

📊 Getting the Inserted ID

cursor.execute("""
    INSERT INTO users (name, age)
    VALUES ('David', 35)
""")
conn.commit()

# Get the auto-generated ID
user_id = cursor.lastrowid
print(f"Inserted user with ID: {user_id}")

🔄 INSERT OR REPLACE

Skip errors if data already exists:

cursor.execute("""
    INSERT OR REPLACE INTO users (id, name, email)
    VALUES (1, 'Alice Updated', 'alice.new@example.com')
""")
conn.commit()

📋 INSERT with Default Values

# Table has DEFAULT values
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    views INTEGER DEFAULT 0
)

# Only specify required columns
cursor.execute("""
    INSERT INTO posts (title)
    VALUES ('My First Post')
""")
conn.commit()

🔗 Inserting with Foreign Keys

# Insert user first
cursor.execute("""
    INSERT INTO users (name, email)
    VALUES ('Alice', 'alice@example.com')
""")
user_id = cursor.lastrowid

# Insert post referencing user
cursor.execute("""
    INSERT INTO posts (user_id, title, content)
    VALUES (?, 'My Post', 'Content here')
""", (user_id,))

conn.commit()

🚨 Handling Insert Errors

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        email TEXT UNIQUE,
        name TEXT NOT NULL
    )
""")

# Try to insert duplicate email
try:
    cursor.execute("""
        INSERT INTO users (email, name)
        VALUES ('alice@example.com', 'Alice')
    """)
    cursor.execute("""
        INSERT INTO users (email, name)
        VALUES ('alice@example.com', 'Another Alice')
    """)
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"Insert failed: {e}")
    conn.rollback()

🔑 Key Takeaways

• ✅ Use INSERT to add rows to tables

• ✅ Always use ? placeholders for safety

• ✅ Use executemany() for batch inserts (100x faster!)

• ✅ Always commit() after inserting

• ✅ Use lastrowid to get the inserted ID

• ✅ Handle IntegrityError for constraint violations

• ✅ Use INSERT OR REPLACE to avoid duplicates

Ready to practice? Challenges | Quiz