MySQL Insert in Python

Inserting data into a MySQL table from Python requires three things: a live connection, a parameterized SQL statement, and a call to commit(). This chapter covers single-row inserts, bulk inserts with executemany(), retrieving the auto-generated ID of the new row, handling duplicates with ON DUPLICATE KEY UPDATE, and the gotchas that catch beginners.

Prerequisites

Before running any example here, make sure you have:

• Python 3.x and a running MySQL server
• mysql-connector-python installed:

pip install mysql-connector-python

• A database and a customers table already created — see MySQL Create Database and MySQL Create Table if you need to set these up first.

The examples assume this table definition:

CREATE TABLE IF NOT EXISTS customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  address VARCHAR(255)
);

Connecting to MySQL

Every operation starts with a connection object. Pass your host, credentials, and database name to mysql.connector.connect(), then create a cursor:

import mysql.connector

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

mycursor = mydb.cursor()

The cursor object sends SQL to the server. Reuse the same connection for multiple statements rather than reconnecting each time.

Inserting a Single Row

Use the SQL INSERT INTO statement with %s placeholders and pass the actual values as a tuple. Never build the query string with Python's % or f-string formatting — that opens your code to SQL injection.

import mysql.connector
from mysql.connector import Error

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

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

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

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

except Error as e:
  print(f"Error: {e}")
  mydb.rollback()

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()

Key points:

• %s is the placeholder syntax for mysql-connector-python regardless of the column's data type (strings, integers, dates all use %s).
• mydb.commit() is required — without it, the insert is never written to disk. MySQL wraps DML statements in implicit transactions; you must commit to finalize them.
• mydb.rollback() in the except block undoes any partial changes if the statement fails.

Retrieving the Auto-Increment ID

After a successful insert, mycursor.lastrowid holds the AUTO_INCREMENT primary key that MySQL assigned to the new row:

import mysql.connector
from mysql.connector import Error

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

  sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
  val = ("Alice", "Maple Street 7")

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

  print("Inserted row ID:", mycursor.lastrowid)

except Error as e:
  print(f"Error: {e}")
  mydb.rollback()

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()

Example output:

Inserted row ID: 1

The value is 0 if the table has no AUTO_INCREMENT column. Use lastrowid to immediately reference the new record in related tables (for example, to insert a matching row into an orders table).

Inserting Multiple Rows

executemany() sends a list of value tuples in a single round-trip to the server, which is far more efficient than calling execute() in a loop:

import mysql.connector
from mysql.connector import Error

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

  sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
  vals = [
    ("Peter",   "Lowstreet 4"),
    ("Amy",     "Apple St 652"),
    ("Hannah",  "Mountain 21"),
    ("Michael", "Valley 345"),
    ("Sandy",   "Ocean Blvd 2"),
    ("Betty",   "Green Grass 1"),
    ("Richard", "Sky St 331"),
    ("Susan",   "One Way 98"),
    ("Vicky",   "Yellow Garden 2"),
    ("Ben",     "Park Lane 38"),
    ("William", "Central St 954"),
    ("Chuck",   "Main Road 989"),
    ("Viola",   "Sideway 1633"),
  ]

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

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

except Error as e:
  print(f"Error: {e}")
  mydb.rollback()

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()

Example output:

13 records inserted.

mycursor.rowcount after executemany() returns the total number of rows affected across all tuples.

When to prefer executemany() over a loop

For very large datasets (tens of thousands of rows), consider batching into chunks of 500–1000 rows so a single failure does not discard the entire operation.

Inserting Without Failing on Duplicates

If your table has a UNIQUE constraint and you try to insert a row that already exists, MySQL raises a duplicate-key error. Two common strategies avoid this:

INSERT IGNORE

INSERT IGNORE silently skips rows that violate a unique constraint:

sql = "INSERT IGNORE INTO customers (name, address) VALUES (%s, %s)"
mycursor.execute(sql, ("John", "Highway 21"))
mydb.commit()
print(mycursor.rowcount, "row(s) affected")  # 0 if row already existed

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE performs an update when the unique key already exists, making the statement an "upsert" (insert-or-update):

sql = """
  INSERT INTO customers (name, address)
  VALUES (%s, %s)
  ON DUPLICATE KEY UPDATE address = VALUES(address)
"""
mycursor.execute(sql, ("John", "New Address 5"))
mydb.commit()
# rowcount is 1 for insert, 2 for update, 0 if row existed but was unchanged
print(mycursor.rowcount, "row(s) affected")

Use ON DUPLICATE KEY UPDATE when you want the latest value stored regardless of whether the row was new or existing.

Inserting Data from a Dictionary

When your data comes as a list of dictionaries (for example, from a parsed JSON payload), you can build the SQL and values dynamically:

import mysql.connector
from mysql.connector import Error

customers = [
  {"name": "Eva",   "address": "Elm Street 3"},
  {"name": "Oscar", "address": "Birch Lane 9"},
]

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

  sql = "INSERT INTO customers (name, address) VALUES (%(name)s, %(address)s)"
  mycursor.executemany(sql, customers)
  mydb.commit()

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

except Error as e:
  print(f"Error: {e}")
  mydb.rollback()

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()

Named placeholders (%(name)s) make the intent clearer when dictionaries are involved and reduce the risk of misaligning positional values.

Common Mistakes to Avoid

Forgetting commit() — The insert appears to work (no error) but no data is saved. Always call mydb.commit() after DML statements.

Building SQL with string formatting — Using f-strings or % formatting to embed user input is the single most common source of SQL injection. Always pass values as a second argument to execute().

Leaving connections open — Use a finally block (or a context manager) to ensure cursor.close() and mydb.close() are always called.

Using INSERT when the table has not been created yet — You will get a Table 'mydatabase.customers' doesn't exist error. Run CREATE TABLE first or verify with SHOW TABLES. See MySQL Create Table.

What to Do Next

Once your rows are inserted, you will typically want to read them back. See MySQL Select for SELECT queries, MySQL Where for filtering, and MySQL Update for modifying existing rows.