Python MySQL Tutorial – How to Insert Data into a Table Using Python

Last updated 5 months, 1 week ago | 479 views 75     5

Tags:- MySQL Python

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

Problem Cause Solution
Data not saved Forgot db.commit() Always call db.commit() after insert
SQL syntax error Incorrect query or missing %s Double-check SQL and placeholders
Wrong number of values Values don't match placeholders Ensure tuples match column count
Special characters break query Manual string formatting Use %s placeholders instead

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

Task Command
Insert one row cursor.execute()
Insert multiple rows cursor.executemany()
Save changes db.commit()
Get last inserted ID cursor.lastrowid

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.