MySQL基础教程（三十九）MySQL之Python 连接与使用：Python与MySQL的梦幻联动，保姆级避坑指南，从入门到“弃坑”

在数据驱动的时代，Python与MySQL的组合已成为开发者的标准工具箱。无论是数据分析、Web开发还是自动化脚本，掌握二者的高效联动至关重要。本文将带你深入MySQL的Python操作核心，揭示从基础连接到高级ORM使用的全貌。

一、驱动选择：MySQL-connector还是PyMySQL？

Python连接MySQL主要依赖两大驱动：官方提供的mysql-connector-python和第三方库PyMySQL。两者在功能上相似，但存在细微差异：

# 使用mysql-connector-python
import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# 使用PyMySQL
import pymysql
conn = pymysql.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database",
    cursorclass=pymysql.cursors.DictCursor  # 返回字典形式结果
)

mysql-connector-python完全遵循Python数据库API规范，而PyMySQL兼容MySQL-python的替代方案。对于新项目，建议使用官方驱动；需要兼容旧项目时，PyMySQL是更好选择。

二、连接管理：连接池的必要性

直接创建连接的成本高昂，在生产环境中必须使用连接池：

from mysql.connector import pooling

dbconfig = {
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "your_database"
}

connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    **dbconfig
)

# 从连接池获取连接
conn = connection_pool.get_connection()

三、CRUD操作与防注入实践

查询操作示例：

def query_user(user_id):
    try:
        with conn.cursor() as cursor:
            # 使用参数化查询防止SQL注入
            sql = "SELECT * FROM users WHERE id = %s"
            cursor.execute(sql, (user_id,))
            result = cursor.fetchall()
            return result
    except Exception as e:
        print(f"查询错误: {e}")

插入操作与事务处理：

def insert_user(user_data):
    try:
        with conn.cursor() as cursor:
            sql = """INSERT INTO users (name, email, age) 
                     VALUES (%s, %s, %s)"""
            cursor.execute(sql, user_data)
            conn.commit()  # 提交事务
            return cursor.lastrowid
    except Exception as e:
        conn.rollback()  # 回滚事务
        print(f"插入错误: {e}")

四、ORM利器：SQLAlchemy实战

对于复杂应用，推荐使用ORM框架SQLAlchemy：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(100))

# 创建引擎和会话
engine = create_engine('mysql+mysqlconnector://user:password@localhost/db')
Session = sessionmaker(bind=engine)
session = Session()

# 查询所有用户
users = session.query(User).all()

五、性能优化与常见陷阱

使用索引优化查询：对WHERE和JOIN子句中的字段建立索引
批量操作减少IO：使用executemany()进行批量插入
适时关闭连接：避免连接泄漏导致资源耗尽

# 批量插入示例
def bulk_insert_users(users_list):
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
            cursor.executemany(sql, users_list)
            conn.commit()
    except Exception as e:
        conn.rollback()
        print(f"批量插入错误: {e}")