Once you've created your MySQL tables, the next step is to insert data into them. Python makes it easy to interact with a MySQL database using the mysql-connector-python package. In this tutorial, you'll learn how to insert data into MySQL tables step by step using Python.

Table of Contents

1. Prerequisites

2. Install MySQL Connector

3. Connect to a MySQL Database

4. Insert a Single Record

5. Insert Multiple Records

6. Use lastrowid to Get Inserted ID

7. Commit Transactions

8. Full Working Example

9. Tips and Common Pitfalls

✅ 1. Prerequisites

Make sure you have:

• Python installed

• MySQL server running

• A MySQL database and table created

• The mysql-connector-python library installed

2. Install MySQL Connector

Install the connector using pip:

pip install mysql-connector-python

3. Connect to a MySQL Database

Use Python to establish a connection to your MySQL database:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

4. Insert a Single Record

Use the INSERT INTO SQL command to add data to your table.

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = ("Alice", "[email protected]", 30)

cursor.execute(sql, val)
db.commit()

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

Always use placeholders (%s) to avoid SQL injection.

5. Insert Multiple Records

You can insert multiple records at once using executemany():

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = [
    ("Bob", "[email protected]", 25),
    ("Charlie", "[email protected]", 28),
    ("Daisy", "[email protected]", 22)
]

cursor.executemany(sql, values)
db.commit()

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

6. Get the ID of the Inserted Row

After inserting a record, use cursor.lastrowid to get the ID of the inserted row:

print("Last inserted ID:", cursor.lastrowid)

✅ 7. Don't Forget to Commit

After any insert operation, you must commit the transaction using db.commit() to save changes to the database.

8. Full Working Example

import mysql.connector
from mysql.connector import Error

def insert_users():
    try:
        # Connect to database
        db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="mydatabase"
        )

        cursor = db.cursor()

        # Insert multiple users
        sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
        values = [
            ("John", "[email protected]", 35),
            ("Jane", "[email protected]", 29),
            ("Mike", "[email protected]", 40)
        ]

        cursor.executemany(sql, values)
        db.commit()

        print(cursor.rowcount, "records inserted.")
        print("Last inserted ID:", cursor.lastrowid)

    except Error as e:
        print("Error:", e)

    finally:
        if db.is_connected():
            db.close()
            print("Connection closed.")

# Run the function
insert_users()

⚠️ 9. Common Pitfalls

Tips and Best Practices

• ✅ Always use parameterized queries to protect against SQL injection.

• ✅ Use executemany() for batch inserts — it's faster and cleaner.

• ✅ Validate data before inserting into the database.

• ✅ Keep column names consistent with your data dictionary.

• ✅ Wrap your database code in try-except-finally blocks for safe execution.

Summary Table

Final Thoughts

Inserting data into a MySQL table with Python is simple and powerful using mysql-connector-python. Whether you’re adding a single row or multiple records, using proper practices like parameterized queries and transaction management ensures your code is efficient and secure.