September 11, 2024

Python Tutorials

Python MySQL Insert Operation

Inserting data into a MySQL database using Python is a common task when working with databases. The mysql-connector-python library provides an easy way to perform insert operations. This guide will walk you through the process of connecting to a MySQL database and inserting data into a table using Python.

1. Prerequisites

Before you can insert data into a MySQL database using Python, ensure that you have the following:

  • MySQL server installed and running on your system.
  • Python installed on your system.
  • The mysql-connector-python library installed. You can install it using pip:
pip install mysql-connector-python

2. Connecting to the MySQL Database

To insert data, you first need to establish a connection to the MySQL database.

2.1. Example: Connecting to MySQL Database

import mysql.connector
from mysql.connector import Error

try:
    # Connect to the MySQL database
    connection = mysql.connector.connect(
        host="localhost",         # Database host (e.g., localhost)
        user="root",              # MySQL username
        password="your_password", # MySQL password
        database="test_db"        # Name of the database to connect to
    )

    if connection.is_connected():
        print("Successfully connected to the database")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

This script connects to the test_db database on the MySQL server running on localhost. If the connection is successful, a confirmation message is printed, and the connection is closed.

3. Inserting Data into a Table

Once connected to the MySQL database, you can insert data into a table using the INSERT INTO SQL statement. You will need a cursor object to execute the SQL statement.

3.1. Example: Inserting a Single Record

# Re-establish the connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="test_db"
)

# Create a cursor object
cursor = connection.cursor()

# SQL query to insert a new record
sql_query = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("John Doe", "john@example.com")

try:
    # Execute the query
    cursor.execute(sql_query, values)

    # Commit the transaction
    connection.commit()

    print(f"Record inserted successfully. ID: {cursor.lastrowid}")

except Error as e:
    print(f"Error: {e}")
    connection.rollback()

finally:
    # Close the cursor and connection
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

This script inserts a new record into the users table. The cursor.execute() method is used to execute the INSERT INTO statement, and the connection.commit() method is called to save the changes. If an error occurs, the transaction is rolled back.

3.2. Example: Inserting Multiple Records

You can also insert multiple records into a table in a single query using the executemany() method.

# Re-establish the connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="test_db"
)

# Create a cursor object
cursor = connection.cursor()

# SQL query to insert multiple records
sql_query = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = [
    ("Alice Smith", "alice@example.com"),
    ("Bob Johnson", "bob@example.com"),
    ("Charlie Brown", "charlie@example.com")
]

try:
    # Execute the query
    cursor.executemany(sql_query, values)

    # Commit the transaction
    connection.commit()

    print(f"{cursor.rowcount} records inserted successfully.")

except Error as e:
    print(f"Error: {e}")
    connection.rollback()

finally:
    # Close the cursor and connection
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

This script inserts multiple records into the users table using the executemany() method. The list of values contains tuples representing each record to be inserted. The transaction is committed if successful, or rolled back in case of an error.

4. Handling Errors

It’s important to handle potential errors during database operations, such as connection issues or SQL syntax errors. Use try-except blocks to catch and handle these errors gracefully.

4.1. Example: Handling Insert Errors

import mysql.connector
from mysql.connector import Error

try:
    # Re-establish the connection
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="test_db"
    )

    # Create a cursor object
    cursor = connection.cursor()

    # Attempt to insert a duplicate record to trigger an error
    sql_query = "INSERT INTO users (name, email) VALUES (%s, %s)"
    values = ("John Doe", "john@example.com")

    cursor.execute(sql_query, values)

    # Commit the transaction
    connection.commit()

    print("Record inserted successfully")

except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.ER_DUP_ENTRY:
        print("Error: Duplicate entry")
    else:
        print(f"Error: {err}")
    connection.rollback()

finally:
    # Close the cursor and connection
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

This script demonstrates how to handle errors when inserting data. It attempts to insert a duplicate record, which triggers an error. The error is caught and handled, and the transaction is rolled back.

5. Best Practices

When inserting data into a MySQL database using Python, consider the following best practices:

  • Use Parameterized Queries: Always use parameterized queries to prevent SQL injection attacks.
  • Handle Exceptions: Use try-except blocks to catch and handle errors, ensuring that your program can respond gracefully to issues like connection failures or SQL errors.
  • Commit Transactions: Remember to commit transactions after inserting data to ensure that changes are saved.
  • Close Connections: Always close the database connection and cursor after completing your operations to free up resources.

You May Like