环境准备

在开始之前，请确保你已经安装了Python和MySQL数据库。此外，为了能够从Python连接到MySQL，你需要安装mysql-connector-python库。可以通过以下命令安装：

pip install mysql-connector-python

连接到MySQL数据库

在进行数据写入之前，我们需要建立与MySQL数据库的连接。以下是连接数据库的基本代码：

import mysql.connector
from mysql.connector import Error

def create_connection():
    """创建数据库连接"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database_name',  # 替换为你的数据库名
            user='your_username',           # 替换为你的用户名
            password='your_password'        # 替换为你的密码
        )
        print("MySQL Database connection successful")
        return connection
    except Error as e:
        print(f"The error '{e}' occurred")

# 使用函数创建连接
conn = create_connection()

插入数据

一旦建立了连接，我们就可以开始插入数据了。以下是一个插入数据的例子，假设我们有一个名为employees的表，我们想要向其中插入员工信息：

def insert_employee(conn, employee):
    """插入员工信息到数据库"""
    query = "INSERT INTO employees (name, position, department) VALUES (%s, %s, %s)"
    cursor = conn.cursor()
    try:
        cursor.execute(query, employee)
        conn.commit()  # 提交事务
        print("Employee inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

# 插入数据的示例
employee = ('John Doe', 'Software Engineer', 'Development')
insert_employee(conn, employee)

批量插入数据

在实际应用中，我们可能需要批量插入多条数据。以下是批量插入数据的示例：

def insert_employees(conn, employees):
    """批量插入员工信息"""
    query = "INSERT INTO employees (name, position, department) VALUES (%s, %s, %s)"
    cursor = conn.cursor()
    try:
        for employee in employees:
            cursor.execute(query, employee)
        conn.commit()  # 提交事务
        print("Employees inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

# 批量插入数据的示例
employees = [
    ('Jane Doe', 'Project Manager', 'Development'),
    ('Alice Johnson', 'Data Analyst', 'Analytics'),
    ('Mike Smith', 'UX Designer', 'Design')
]
insert_employees(conn, employees)

关闭数据库连接

完成数据写入后，不要忘记关闭游标和数据库连接：

conn.close()

完整代码

import mysql.connector
from mysql.connector import Error

# 创建数据库连接
def create_connection():
    """创建数据库连接"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database_name',  # 替换为你的数据库名
            user='your_username',           # 替换为你的用户名
            password='your_password'        # 替换为你的密码
        )
        print("MySQL Database connection successful")
        return connection
    except Error as e:
        print(f"The error '{e}' occurred")
        return None

# 插入员工信息到数据库
def insert_employee(conn, employee):
    """插入员工信息"""
    query = "INSERT INTO employees (name, position, department) VALUES (%s, %s, %s)"
    cursor = conn.cursor()
    try:
        cursor.execute(query, employee)
        conn.commit()  # 提交事务
        print("Employee inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

# 批量插入员工信息
def insert_employees(conn, employees):
    """批量插入员工信息"""
    query = "INSERT INTO employees (name, position, department) VALUES (%s, %s, %s)"
    cursor = conn.cursor()
    try:
        for employee in employees:
            cursor.execute(query, employee)
        conn.commit()  # 提交事务
        print("Employees inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

# 主程序
if __name__ == '__main__':
    # 使用函数创建连接
    conn = create_connection()
    if conn is not None:
        # 插入数据的示例
        employee = ('John Doe', 'Software Engineer', 'Development')
        insert_employee(conn, employee)

        # 批量插入数据的示例
        employees = [
            ('Jane Doe', 'Project Manager', 'Development'),
            ('Alice Johnson', 'Data Analyst', 'Analytics'),
            ('Mike Smith', 'UX Designer', 'Design')
        ]
        insert_employees(conn, employees)

        # 关闭数据库连接
        conn.close()

结论

通过上述步骤，我们可以看到Python提供了一种简单而有效的方式来将数据写入MySQL数据库。无论是单条数据插入还是批量数据插入，Python都能够轻松应对。这不仅提高了开发效率，也使得数据管理变得更加灵活和强大。