博客站

在现代数据处理应用中，Python与MySQL的结合是一种常见且强大的技术组合。本文将详细介绍如何使用Python脚本将数据插入MySQL数据库，涵盖从环境配置到实际操作的完整流程。

准备工作

在开始之前，确保您已安装以下组件：

• Python 3.x
• MySQL服务器
• Python的MySQL连接库（如PyMySQL或mysql-connector-python）

可以通过pip安装所需库：

pip install pymysql
# 或
pip install mysql-connector-python

建立数据库连接

首先，我们需要建立与MySQL数据库的连接：

import pymysql

# 创建数据库连接
connection = pymysql.connect(
    host='localhost',  # 数据库服务器地址
    user='your_username',  # 用户名
    password='your_password',  # 密码
    database='your_database',  # 数据库名
    charset='utf8mb4',  # 字符编码
    cursorclass=pymysql.cursors.DictCursor  # 返回字典格式的游标
)

print("数据库连接成功!")

创建数据表（可选）

如果目标表不存在，我们可以先创建它：

try:
    with connection.cursor() as cursor:
        # 创建表的SQL语句
        sql = """
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            email VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        cursor.execute(sql)
    connection.commit()
    print("表创建成功或已存在")
except Exception as e:
    print(f"创建表时出错: {e}")

插入单条数据

最基本的操作是插入单条记录：

try:
    with connection.cursor() as cursor:
        # 插入数据的SQL语句
        sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
        cursor.execute(sql, ('张三', 'zhangsan@example.com'))
    
    # 提交事务
    connection.commit()
    print("数据插入成功!")
except Exception as e:
    # 发生错误时回滚
    connection.rollback()
    print(f"插入数据时出错: {e}")

批量插入数据

对于大量数据，批量插入效率更高：

# 准备批量数据
users_data = [
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com'),
    ('赵六', 'zhaoliu@example.com')
]

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
        cursor.executemany(sql, users_data)
    connection.commit()
    print(f"成功批量插入 {len(users_data)} 条记录")
except Exception as e:
    connection.rollback()
    print(f"批量插入时出错: {e}")

使用上下文管理器确保连接关闭

为确保数据库连接正确关闭，推荐使用上下文管理器：

from contextlib import contextmanager

@contextmanager
def get_db_connection():
    conn = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )
    try:
        yield conn
    finally:
        conn.close()

# 使用示例
with get_db_connection() as connection:
    with connection.cursor() as cursor:
        cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
                      ('钱七', 'qianqi@example.com'))
    connection.commit()

错误处理与日志记录

健壮的程序需要完善的错误处理和日志记录：

import logging

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def safe_insert(data):
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
                cursor.executemany(sql, data)
            conn.commit()
            logging.info(f"成功插入 {len(data)} 条记录")
            return True
    except pymysql.MySQLError as e:
        logging.error(f"数据库错误: {e}")
        return False
    except Exception as e:
        logging.error(f"未知错误: {e}")
        return False

性能优化技巧

1. 使用批量插入：如示例所示，executemany比循环执行单条插入快得多
2. 调整事务提交频率：对于大量数据，可以每1000条提交一次
3. 使用连接池：考虑使用DBUtils等库管理连接池
4. 准备语句：重复使用相同的SQL语句可以提高性能

完整示例代码

import pymysql
from contextlib import contextmanager
import logging

# 日志配置
logging.basicConfig(level=logging.INFO)

# 数据库连接上下文管理器
@contextmanager
def get_db_connection():
    conn = pymysql.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        yield conn
    finally:
        conn.close()

# 批量插入函数
def batch_insert_users(user_list):
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
                cursor.executemany(sql, user_list)
            conn.commit()
        logging.info(f"成功插入 {len(user_list)} 条用户记录")
        return True
    except pymysql.MySQLError as e:
        logging.error(f"数据库错误: {e}")
        return False
    except Exception as e:
        logging.error(f"未知错误: {e}")
        return False

if __name__ == "__main__":
    # 测试数据
    users = [
        ('张三', 'zhangsan@example.com'),
        ('李四', 'lisi@example.com'),
        ('王五', 'wangwu@example.com')
    ]
    
    # 执行插入
    batch_insert_users(users)

总结

本文介绍了使用Python操作MySQL数据库进行数据插入的完整流程，包括：

• 环境配置和库安装
• 数据库连接建立与管理
• 单条和批量数据插入
• 错误处理与日志记录
• 性能优化技巧

通过掌握这些基础知识，您可以轻松地将Python与MySQL结合，构建高效的数据处理应用。根据实际需求，您还可以进一步探索ORM工具如SQLAlchemy或Django ORM等更高级的数据库操作方式。