Insert Operation

To insert data into a MySQL database using Python, the MySQL Connector or similar libraries such as pymysql or SQLAlchemy must be used. A step-by-step guide for how to insert a row into a MySQL table from Python using MySQL Connector follows:

1. Install MySQL Connector

Ensure you have the MySQL Connector installed. Use pip to install it if you haven’t already:

pip install mysql-connector-python

2. Setup MySQL Database

Ensure you have a MySQL database and table ready. For example:

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    department VARCHAR(255)
);

3. Python Code for Insert Operation

Here’s a detailed Python example to insert data into the employees table:

Import MySQL Connector

import mysql.connector

Step-by-Step Process

1. Establish a Connection

Use the mysql.connector.connect() method to connect to the database.

conn = mysql.connector.connect(
    host="localhost",       # Replace with your MySQL host
    user="your_username",   # Your MySQL username
    password="your_password", # Your MySQL password
    database="testdb"       # Name of the database
)

2. Create a Cursor Object

A cursor is used to execute SQL commands.

cursor = conn.cursor()

3. Write the SQL Query

Use placeholders (%s) for dynamic data to avoid SQL injection.

sql = "INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)"

4. Define Data to Insert

Prepare the data as a tuple.

val = ("John Doe", 30, "HR")

5. Execute the Query

Use cursor.execute() to execute the query.

cursor.execute(sql, val)

6. Commit the Transaction

Always commit the transaction to save changes in the database.

conn.commit()
print(cursor.rowcount, "record(s) inserted.")

7. Close the Connection

Clean up resources by closing the cursor and connection.

cursor.close()
conn.close()

Full Example Code

import mysql.connector

try:
    # Step 1: Connect to the database
    conn = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="testdb"
    )

    # Step 2: Create a cursor object
    cursor = conn.cursor()

    # Step 3: Define SQL query and data
    sql = "INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)"
    val = ("Alice Smith", 25, "Finance")

    # Step 4: Execute the query
    cursor.execute(sql, val)

    # Step 5: Commit the transaction
    conn.commit()
    print(cursor.rowcount, "record inserted.")

except mysql.connector.Error as err:
    print("Error:", err)

finally:
    # Step 6: Close the connection
    if cursor:
        cursor.close()
    if conn:
        conn.close()

4. Batch Insert for Multiple Records

To insert multiple rows in one query:

val = [
    ("Jane Doe", 28, "IT"),
    ("Mike Ross", 35, "Legal"),
    ("Rachel Zane", 30, "Marketing")
]
cursor.executemany(sql, val)
conn.commit()
print(cursor.rowcount, "records inserted.")

5. Best Practices

• Avoid SQL Injection: Never insert user input directly; always use placeholders (%s).
• Error Handling: Use try.except blocks to catch exceptions.
• Resource Management: Ensure the cursor and connection are closed even if an error occurs (use finally).