MySQL Insert 📥

Inserting Data

Use the INSERT INTO SQL statement to add records to a MySQL table.

✨ Insert Single Record

python

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

spl = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()  # CRITICAL: Must commit to save changes

print(mycursor.rowcount, "record inserted.")

⚠️ Must Commit!

Always call mydb.commit() after INSERT, UPDATE, or DELETE. Without it, changes are not saved!

🔢 Insert Multiple Records

Use executemany() for bulk inserts (much faster than a loop):

python

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2')
]

mycursor.executemany(sql, val)
mydb.commit()

print(mycursor.rowcount, "records inserted.")

🆔 Get Last Inserted ID

python

mycursor.execute(sql, val)
mydb.commit()

last_id = mycursor.lastrowid
print("ID of inserted record:", last_id)

✨ Pro Tip: SQL Injection

Always use parameterized queries (%s placeholders) instead of string formatting (f-strings or +). This prevents SQL injection attacks!