PythonHow-ToBeginner · 3 min read

How to Insert Data into MySQL Using Python Easily

To insert data into MySQL using Python, use the mysql-connector-python library to connect to the database, create a cursor, and execute an INSERT SQL statement with cursor.execute(). Finally, commit the transaction with connection.commit() to save changes.

📐

Syntax

Here is the basic syntax to insert data into a MySQL table using Python:

Connect: Establish a connection to the MySQL database.
Create Cursor: Get a cursor object to execute SQL commands.
Execute: Run an INSERT INTO SQL statement with values.
Commit: Save the changes to the database.
Close: Close the cursor and connection.

python

import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

cursor = connection.cursor()

sql = "INSERT INTO table_name (column1, column2) VALUES (%s, %s)"
values = ('value1', 'value2')

cursor.execute(sql, values)
connection.commit()

cursor.close()
connection.close()

💻

Example

This example shows how to insert a new user into a users table with columns name and email. It connects to the database, inserts the data, commits the transaction, and closes the connection.

python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='password123',
        database='testdb'
    )

    cursor = connection.cursor()

    sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
    values = ('Alice', 'alice@example.com')

    cursor.execute(sql, values)
    connection.commit()

    print(f"Inserted {cursor.rowcount} row(s) successfully.")

except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

Output

Inserted 1 row(s) successfully.

⚠️

Common Pitfalls

Common mistakes when inserting data into MySQL using Python include:

Not calling connection.commit(), so changes are not saved.
Using string formatting to build SQL queries, which can cause SQL injection risks.
Not closing the cursor and connection, leading to resource leaks.
Incorrect database credentials or missing database/table causing connection errors.

python

import mysql.connector

# Wrong way: vulnerable to SQL injection and no commit
connection = mysql.connector.connect(host='localhost', user='root', password='password123', database='testdb')
cursor = connection.cursor()
name = "Alice"
email = "alice@example.com"
query = f"INSERT INTO users (name, email) VALUES ('{name}', '{email}')"
cursor.execute(query)
# Missing connection.commit() here

# Right way: use parameterized queries and commit
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = (name, email)
cursor.execute(sql, values)
connection.commit()

cursor.close()
connection.close()

📊

Quick Reference

Remember these key points when inserting data into MySQL with Python:

Use mysql-connector-python or similar library.
Always use parameterized queries to avoid SQL injection.
Call connection.commit() after execute().
Close cursor and connection to free resources.

✅

Key Takeaways

Use parameterized queries with placeholders to safely insert data.

Always call connection.commit() to save your changes.

Close your cursor and connection to avoid resource leaks.

Handle exceptions to catch database errors gracefully.

Use a reliable MySQL connector library like mysql-connector-python.