← Back to Python Course | Chapter 15: Python & MySQL | Lesson 4 of 12

Python MySQL Insert Into Table

New rows are added to a table using an INSERT INTO SQL statement, executed through the cursor -- crucially, using parameterized placeholders (%s) for the actual values rather than building the SQL string by hand, which protects against SQL injection.

In this page:

  1. Basic INSERT Statement
  2. Committing the Transaction
  3. Getting the New Row's ID
  4. Inserting Multiple Rows at Once
  5. Handling Insert Errors Gracefully
Syntax
python 
sql = "INSERT INTO table_name (column1, column2) VALUES (%s, %s)"
cursor.execute(sql, (value1, value2))
connection.commit()

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

python 
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))  # values passed safely as a tuple, not embedded in the string
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

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("INSERT INTO customers (name) VALUES (%s)", ("John",))
conn.commit()  # without this, the insert would not be permanently saved
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

python 
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)  # auto-generated primary key of the row just inserted

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

python 
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)  # inserts all rows in one batched call
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

python 
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()  # discards the failed, partial transaction
    print("Insert failed, rolled back:", e)
Related Topics

Python MySQL Create Table · Python MySQL Select From Table · Python MySQL Drop Table · Python MySQL Update Table

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Building the SQL string with Python f-strings or % formatting instead of using parameterized placeholders (%s), which opens the door to SQL injection vulnerabilities.
  2. 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.
  3. 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.
Chapter Summary
  • 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.
Browser Support

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:

← Previous Lesson Python MySQL Create Table Next Lesson → Python MySQL Select From Table

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.