Python MySQL Insert Into Table
In this page:
Basic INSERT Statement
cursor.execute("INSERT INTO customers (name, address) VALUES (%s, %s)", (name, address)) adds one new row to the customers table, with the actual values supplied safely as a separate tuple argument rather than embedded directly in the SQL string.
Note: Always use %s placeholders with a separate values tuple, never Python string formatting, to build INSERT statements -- this is the single most important MySQL security habit to build.
Warning: The number of %s placeholders in the SQL string must exactly match the number of items in the values tuple, or Python raises an error before the query even reaches MySQL.
Example: Basic INSERT Statement
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
name, address = "John", "Highway 21"
cursor.execute("INSERT INTO customers (name, address) VALUES (%s, %s)", (name, address))
print("Row inserted safely with placeholders")Committing the Transaction
conn.commit() permanently saves any pending INSERT, UPDATE, or DELETE changes to the database -- without calling it, changes made during the current connection are not actually persisted and are lost when the connection closes.
Note: Call conn.commit() immediately after every INSERT/UPDATE/DELETE operation (or in a batch, after a related group of them), rather than assuming it happens automatically.
Warning: Forgetting conn.commit() is one of the most common Python-MySQL mistakes -- the code runs with no errors at all, yet the data mysteriously never appears in the database.
Example: Committing the Transaction
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("INSERT INTO customers (name) VALUES (%s)", ("John",))
conn.commit()
print("Changes permanently saved")Getting the New Row's ID
cursor.lastrowid returns the auto-generated primary key value of the most recently inserted row -- useful when you immediately need that new ID, for example to insert related rows in another table right afterward.
Note: Read cursor.lastrowid immediately after the INSERT and commit, before running any other query on the same cursor, since its value reflects only the most recent insert.
Warning: cursor.lastrowid is only meaningful when the table's primary key is an AUTO_INCREMENT column -- it is not useful for tables with manually-assigned primary keys.
Example: Getting the New Row's ID
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.lastrowid = 1
cursor.execute("INSERT INTO customers (name) VALUES (%s)", ("John",))
conn.commit()
print("New customer id:", cursor.lastrowid)Inserting Multiple Rows at Once
cursor.executemany(sql, list_of_tuples) inserts many rows in a single call, far more efficiently than looping and calling execute() individually for each row, since it batches the network round-trips to the database server.
Note: Use executemany() whenever inserting more than a handful of rows at once -- the performance difference over a loop of individual execute() calls grows significant at scale.
Warning: executemany() still requires every tuple in the list to have the same number of elements as there are %s placeholders in the SQL string.
Example: Inserting Multiple Rows at Once
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
customers = [("Amy", "Apple St"), ("Hannah", "Mountain 21")]
cursor.executemany("INSERT INTO customers (name, address) VALUES (%s, %s)", customers)
conn.commit()
print(len(customers), "rows inserted in one batch")Handling Insert Errors Gracefully
Wrapping an INSERT in a try-except block catching mysql.connector.Error lets you handle failures -- like violating a unique constraint or a foreign key relationship -- without crashing the whole script, and lets you conn.rollback() to discard a failed, partial transaction.
Note: Call conn.rollback() in the except block of a failed insert to cleanly discard any partial changes, keeping the database in a consistent state.
Warning: A failed INSERT inside a larger multi-step transaction can leave things in an inconsistent state if you do not explicitly roll back -- always pair try/except with rollback for write operations.
Example: Handling Insert Errors Gracefully
from unittest.mock import MagicMock
class Error(Exception):
pass
conn = MagicMock()
cursor = conn.cursor()
cursor.execute.side_effect = Error("Duplicate entry")
try:
cursor.execute("INSERT INTO customers (name) VALUES (%s)", ("John",))
conn.commit()
except Error as e:
conn.rollback()
print("Insert failed, rolled back:", e)- Building the SQL string with Python f-strings or % formatting instead of using parameterized placeholders (%s), which opens the door to SQL injection vulnerabilities.
- Forgetting to call conn.commit() after an INSERT, which means the new row is not actually saved to the database -- it silently vanishes when the connection closes.
- Passing a value tuple with the wrong number of elements relative to the number of %s placeholders in the SQL string, causing a mismatch error.
- cursor.execute("INSERT INTO table (cols) VALUES (%s, %s)", (val1, val2)) inserts a new row using safe, parameterized placeholders.
- conn.commit() must be called after any INSERT, UPDATE, or DELETE to actually save the change permanently to the database.
- cursor.lastrowid gives you the auto-generated primary key of the row you just inserted.
Parameterized queries with %s placeholders are the standard, injection-safe approach across all versions of mysql-connector-python.
Chapter Quiz — Complete all 12 topics to unlock
0/12 topics done
Complete these topics first:
- Python MySQL Get Started
- Python MySQL Create Database
- Python MySQL Create Table
- Python MySQL Insert Into Table
- Python MySQL Select From Table
- Python MySQL Select With a Filter
- Python MySQL Order By
- Python MySQL Delete Record
- Python MySQL Drop Table
- Python MySQL Update Table
- Python MySQL Limit
- Python MySQL Join