Python MySQL Tutorial – How to Insert Data into a Table Using Python
Last updated 5 months, 1 week ago | 479 views 75 5

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
-
Prerequisites
-
Install MySQL Connector
-
Connect to a MySQL Database
-
Insert a Single Record
-
Insert Multiple Records
-
Use
lastrowid
to Get Inserted ID -
Commit Transactions
-
Full Working Example
-
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.