Python MySQL: How to Insert Data Into a Table

Inserting data into a MySQL table is a fundamental operation for any database-driven application. With Python, you can programmatically add data to your MySQL database tables. This tutorial will guide you step-by-step on how to insert data into a table using Python.

At The Coding College, our mission is to make programming concepts easy and actionable. Let’s dive into this essential topic!

Prerequisites

Before proceeding, ensure you have the following:

  • Python Installed: Get it from python.org.
  • MySQL Server Installed: Download MySQL from MySQL official site.
  • MySQL Connector Installed: Install the library via pip: 
pip install mysql-connector-python

Steps to Insert Data Into a MySQL Table

Step 1: Connect to the Database

Connect to the MySQL database where your table is located:

import mysql.connector

# Connect to the database
db = mysql.connector.connect(
    host="localhost",       # Hostname
    user="your_username",   # MySQL Username
    password="your_password", # MySQL Password
    database="your_database"  # Database Name
)

print("Connected to the database!")

Replace your_username, your_password, and your_database with your MySQL credentials.

Step 2: Create a Cursor

A cursor object is required to execute SQL commands:

cursor = db.cursor()

Step 3: Write and Execute the SQL Command

Use the INSERT INTO command to add data to the table. Here’s an example of inserting a single record into a users table:

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = ("John Doe", "[email protected]", 30)

cursor.execute(sql, values)
db.commit()  # Commit the changes
print(f"{cursor.rowcount} record inserted successfully!")

Step 4: Insert Multiple Records

To insert multiple records at once, use the executemany() method:

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = [
    ("Jane Smith", "[email protected]", 25),
    ("Alice Brown", "[email protected]", 29),
    ("Bob White", "[email protected]", 35)
]

cursor.executemany(sql, values)
db.commit()  # Commit the changes
print(f"{cursor.rowcount} records inserted successfully!")

Full Python Code Example

Here’s a complete script to insert data into a MySQL table using Python:

import mysql.connector

# Connect to the database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Insert a single record
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = ("John Doe", "[email protected]", 30)
cursor.execute(sql, values)

# Insert multiple records
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = [
    ("Jane Smith", "[email protected]", 25),
    ("Alice Brown", "[email protected]", 29),
    ("Bob White", "[email protected]", 35)
]
cursor.executemany(sql, values)

# Commit changes
db.commit()
print(f"{cursor.rowcount} records inserted successfully!")

# Close the connection
db.close()

Common Errors and Troubleshooting

1. Access Denied Error

Ensure your database credentials are correct and the user has permission to insert data.

2. Column Count Mismatch

Ensure the number of columns in the INSERT INTO statement matches the values provided.

Exercises

Exercise 1: Insert Product Data

Create a table named products with columns id, name, price, and stock. Write a script to insert at least 5 product records.

Exercise 2: Insert User Data From User Input

Modify the script to accept user input for name, email, and age and insert the data into the users table.

Why Choose The Coding College?

At The Coding College, we provide hands-on tutorials that simplify programming concepts. Master Python and MySQL to build robust database-driven applications with ease!

Conclusion

Using Python to insert data into a MySQL table is a critical skill for database management. Whether you’re adding a single record or handling bulk inserts, Python offers the flexibility to automate the process.

Leave a Comment