Python操作MySQL数据库的两种方式实例分析【pymysql和pandas】

前言先说一个标题里的不准确之处pandas 本身并不是一个 MySQL 驱动也没有自己的 数据库连接能力。它自己不会连 MySQL所谓用 pandas 操作 MySQL 实际是 pandas 把这件事委托给别的库要么交给一个 DBAPI 驱动比如pymysql 要么交给 SQLAlchemy 建立连接再交给驱动执行。所以两种方式其实准确的说法是驱动直连方式用pymysql自己建连接、自己执行 SQL、自己取结果想变 DataFrame 再手动构造pandas 方式用pandas.read_sql/DataFrame.to_sql背后由 SQLAlchemy 驱动MySQL 场景下通常还是pymysql干活。两者不是对立的而是底层和上层封装的关系。本文分别演示并说明各自适用场景。 需要强调pymysql与pandas都是第三方库本机没有安装、也没有 Python 解释器 示例无法运行验证只能逐行人工推演。参数细节请以 pymysql 与 Pandas 官方文档为准。一、方式一pymysql 直连pymysql.connect的签名里连接参数都是仅关键字的关键字参数 常用的是host、user、password、database、port、charset。 中文场景建议charsetutf8mb4否则 emoji 和部分生僻字会丢。# 需要 pip install pymysqlPython 3.8import pymysqlconn pymysql.connect(host127.0.0.1,userapp,passwordsecret,databaseshop,port3306,charsetutf8mb4,)try:with conn.cursor() as cur:cur.execute(SELECT id, name FROM users WHERE age %s, (18,))rows cur.fetchall() # 返回元组的元组columns [d[0] for d in cur.description]conn.commit()finally:conn.close()print(columns)print(rows[:3])要点cursor.execute(sql, params)里的%s是参数占位符不是字符串拼接参数用元组或列表传入由驱动负责转义——这是防 SQL 注入的正确姿势。cur.description里每项的第一个元素是列名可用来拿列头。fetchall()取全部、fetchone()取一行、fetchmany(n)取 n 行。增删改之后要conn.commit()否则事务不落库默认autocommitFalse。conn.cursor()也支持with退出时自动关闭游标。把结果转成 DataFrame 只是把columns和rows组装起来# 需要 pip install pymysql pandasimport pandas as pddf pd.DataFrame(rows, columnscolumns)因为fetchall()返回的是元组的元组DataFrame(rows, columnscolumns)正好能吃下这个形状。数据量很大时一次性fetchall()会把全部结果读进内存 此时更该用 pandas 的chunksize分批读。二、方式二pandas SQLAlchemypandas 官方文档给出的连接方式是 SQLAlchemy。MySQL 场景下 连接串形如mysqlpymysql://用户:密码主机/库名后面就是驱动名。# 需要 pip install pandas sqlalchemy pymysqlimport pandas as pdfrom sqlalchemy import create_engineengine create_engine(mysqlpymysql://app:secret127.0.0.1:3306/shop?charsetutf8mb4)# 参数化查询用 params别自己拼字符串df pd.read_sql(SELECT id, name FROM users WHERE age %(age)s,engine,params{age: 18},)print(df.head())read_sql(sql, con, ...)的第一个参数是 SQL、第二个才是连接别写反。 它的params支持序列或字典两种占位风格具体用哪种取决于驱动的 paramstyle。写回数据库用to_sql# pandas 1.0df.to_sql(users_snapshot, engine, if_existsappend, indexFalse)if_exists可取fail默认表已存在就报错、replace覆盖、append追加。indexFalse防止把行号当成一列写进去。对比项pymysql 直连pandas SQLAlchemy依赖只要pymysqlpandassqlalchemy 驱动结果形态元组需自己组装直接是DataFrame适用精细控制事务、游标快速取数、直接分析写入自己拼INSERTto_sql一步参数化%s 元组params三、该选哪一种只要把表取出来分析用read_sql代码最短。需要精细事务控制、逐行处理、调用存储过程用pymysql直连。两者也可以混用pandas 取数做分析pymysql 负责小而频繁的写入。这里有个细节值得特别说明pandas 官方文档在没有 SQLAlchemy 时怎么办这一节里 给出的回退方案是使用sqlite3.Connection标准库自带的 SQLite 驱动并没有承诺接受任意 DBAPI 驱动对象。因此在 MySQL 场景下 把pymysql的原始连接直接丢给read_sql并不是文档推荐的路径 稳妥做法仍然是通过 SQLAlchemy 建 engine再把 engine 交给 pandas。 如果你在旧代码里见到pd.read_sql(sql, pymysql_conn)迁移到 engine 写法更安全。 engine 还有一个附带好处连接池由 SQLAlchemy 统一管理不必每次手动开关连接。四、把结果做成一张汇总# 需要 pip install pandas sqlalchemy pymysqlimport pandas as pdfrom sqlalchemy import create_enginedef load(engine, days: int) - pd.DataFrame:sql (SELECT region, amount, created_at FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL %(days)s DAY))return pd.read_sql(sql, engine, params{days: days})if __name__ __main__:eng create_engine(mysqlpymysql://app:secret127.0.0.1:3306/shop)data load(eng, 30)print(data.groupby(region, as_indexFalse)[amount].sum())常见坑点把用户输入拼进 SQL 字符串❌cur.execute(SELECT * FROM users WHERE name%s % name) 遇到引号就注入是严重安全问题 ✅cur.execute(SELECT * FROM users WHERE name%s, (name,))交给驱动转义read_sql的参数顺序写反❌pd.read_sql(engine, SELECT ...)把连接当成 SQL ✅pd.read_sql(SELECT ..., engine)忘记commit❌ 插入后直接close()数据库里查不到新数据 ✅ 增删改后conn.commit()或理解autocommit的含义字符集没设utf8mb4❌ 存 emoji 或部分中文时报错/变成问号 ✅charsetutf8mb4连接串里也写成?charsetutf8mb4fetchall()拉大表❌ 千万行结果一次性读进内存进程被撑爆 ✅ 用read_sql(..., chunksize...)分批或LIMIT先取样本to_sql默认if_existsfail❌ 第二次运行就报表已存在还以为代码坏了 ✅ 明确写if_existsappend或replace连接串里写明文密码并提交进仓库❌ 把mysqlpymysql://root:真实密码...直接写进代码 ✅ 密码从环境变量或配置文件读取敏感信息不进版本库用 pandas 时以为它自带驱动❌ 只装了 pandas 就报缺少模块 pymysql需要 SQLAlchemy ✅ 记住 pandas 只是上层必须另装 SQLAlchemy 和驱动器总结主题结论标题中的说法pandas 不是数据库驱动pandas 方式实为 pandas SQLAlchemy 驱动直连方式pymysql.connectcursor.executefetchallpandas 方式read_sql(sql, con, params...)/to_sql安全要点一律参数化绝不拼字符串内存要点大结果集用chunksize或LIMIT事务要点增删改要commit注意字符集掌握这两种方式的关键是理解分层pymysql 管连接和事务pandas 管取数和分析。 把密码、字符集、参数化这三件小事做对剩下的就是写 SQL 和拼报表了。