Python操作MySQL：从基础到高级实践指南

1. Python操作MySQL基础入门MySQL作为最流行的开源关系型数据库之一与Python的结合在数据处理领域应用广泛。我使用Python连接MySQL已有五年实战经验今天系统梳理一下核心操作要点。Python连接MySQL主要通过数据库驱动实现目前主流的选择有mysql-connector-pythonMySQL官方驱动PyMySQL纯Python实现MySQLdbC扩展驱动提示新手建议从mysql-connector-python开始它由Oracle官方维护兼容性最好安装也最简单。1.1 环境准备与驱动安装首先确保已安装MySQL服务建议5.7或8.0版本。通过pip安装驱动pip install mysql-connector-python验证安装是否成功import mysql.connector print(mysql.connector.__version__) # 应输出版本号如8.0.331.2 建立数据库连接创建连接对象时有几个关键参数需要注意import mysql.connector config { host: localhost, user: your_username, password: your_password, database: your_database, # 可选 port: 3306, # 默认端口 charset: utf8mb4, # 推荐字符集 autocommit: True # 自动提交设置 } conn mysql.connector.connect(**config)重要生产环境切勿将密码硬编码在代码中应使用环境变量或配置中心管理敏感信息。2. 核心CRUD操作详解2.1 创建表与插入数据典型建表与插入操作示例cursor conn.cursor() # 建表 create_table_sql CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 cursor.execute(create_table_sql) # 插入单条 insert_sql INSERT INTO users (username, email) VALUES (%s, %s) val (john_doe, johnexample.com) cursor.execute(insert_sql, val) # 批量插入 users [ (alice, aliceexample.com), (bob, bobexample.com) ] cursor.executemany(insert_sql, users) conn.commit() # 非autocommit模式需手动提交2.2 查询与结果处理查询结果的几种处理方式# 基础查询 cursor.execute(SELECT * FROM users WHERE username LIKE %s, (j%,)) rows cursor.fetchall() # 获取所有记录 # 字典形式返回结果 cursor conn.cursor(dictionaryTrue) cursor.execute(SELECT id, username FROM users) for row in cursor: print(fID: {row[id]}, Name: {row[username]}) # 分页查询 page_size 10 page_num 1 offset (page_num - 1) * page_size cursor.execute( SELECT * FROM users ORDER BY id LIMIT %s OFFSET %s, (page_size, offset) )3. 高级特性与性能优化3.1 事务管理与异常处理正确的异常处理模板try: conn.start_transaction() cursor.execute(UPDATE accounts SET balance balance - 100 WHERE user_id 1) cursor.execute(UPDATE accounts SET balance balance 100 WHERE user_id 2) conn.commit() except mysql.connector.Error as err: print(fError occurred: {err}) conn.rollback() finally: cursor.close() conn.close()3.2 连接池配置高并发场景应使用连接池from mysql.connector import pooling dbconfig { host: localhost, user: user, password: password, database: test } connection_pool pooling.MySQLConnectionPool( pool_namemypool, pool_size5, **dbconfig ) # 使用连接 conn connection_pool.get_connection() cursor conn.cursor() # 执行操作... cursor.close() conn.close() # 实际是返回到连接池4. 常见问题解决方案4.1 编码问题处理UTF-8乱码的典型解决方案确保MySQL服务端字符集设置为utf8mb4连接参数添加charsetutf8mb4表定义指定DEFAULT CHARSETutf8mb44.2 连接超时优化连接参数建议配置config { pool_size: 5, pool_name: web_pool, pool_reset_session: True, connect_timeout: 30, connection_retry_delay: 5, connection_retries: 3 }4.3 大数据量操作批量插入优化方案# 使用executemanyunbuffered模式 cursor conn.cursor(bufferedFalse) data [(fuser{i}, femail{i}test.com) for i in range(10000)] cursor.executemany(insert_sql, data) conn.commit()5. 安全最佳实践始终使用参数化查询防止SQL注入# 错误做法 cursor.execute(fSELECT * FROM users WHERE id {user_input}) # 正确做法 cursor.execute(SELECT * FROM users WHERE id %s, (user_input,))最小权限原则应用账号只授予必要权限定期更换数据库密码并使用SSL连接敏感数据加密存储如密码应使用bcrypt等哈希算法这套方案在我负责的多个生产系统中稳定运行单表亿级数据量下仍能保持良好性能。实际开发中还需要根据具体业务场景调整优化策略。