当前位置:

首页 > 编程开发 > Python操作MySQL数据库的方法实例

Python操作MySQL数据库的方法实例

前言 数据库是现代应用的基石,MySQL作为关系型数据库的代表,几乎无处不在。今天这篇就来聊聊如何用Python远程操作MySQL,从连接、CRUD、模糊查询、JOIN,到事务、隔离级别和连接池,一步到位。文章里会以两台服务器为例:MySQL服务器:192.168.10.101,Python服务器:

本文目录

    前言

    数据库是现代应用的基石,MySQL作为关系型数据库的代表,几乎无处不在。今天这篇就来聊聊如何用Python远程操作MySQL,从连接、CRUD、模糊查询、JOIN,到事务、隔离级别和连接池,一步到位。文章里会以两台服务器为例:MySQL服务器:192.168.10.101,Python服务器:192.168.10.102。同时,MySQL端创建远程用户、授权、旧认证插件兼容这些前置操作也会一并讲清楚。

    Python操作MySQL数据库的方法实例

    一、安装 Python MySQL 连接库

    1. 安装命令

    先把必要的库装上,两条命令搞定:

    pip install mysql-connector-python
    pip install pymysql

    2. 补充知识点

    • pymysql:纯Python实现的MySQL客户端,跨平台,支持TCP/IP远程连接,轻量好用。
    • dbutils:数据库连接池组件,用来复用连接,减少反复创建/销毁连接的开销。
    • mysql-connector-python:MySQL官方驱动,可以作为pymysql的备选。
    • 生产环境建议固定库版本,避免兼容性问题翻车。

    二、MySQL 服务器端配置(192.168.10.101)

    所有操作都在MySQL命令行下执行(mysql -u root -p)。

    1. 创建远程访问用户

    CREATE USER 'root'@'192.168.10.102' IDENTIFIED BY 'pwd123';

    2. 授权数据库权限

    GRANT REPLICATION  ON *.* TO 'root'@'192.168.10.102';

    3. 旧密码认证插件兼容

    MySQL 8.0+默认认证插件是caching_sha2_password,pymysql不支持它,所以需要改成老插件:

    ALTER USER 'root'@'192.168.10.102' IDENTIFIED WITH mysql_native_password BY 'pwd123';

    4. 刷新权限

    FLUSH PRIVILEGES;

    5. 添加数据

    创建测试数据库和表,插入一些示例数据:

    -- 创建数据库
    CREATE DATABASE IF NOT EXISTS testdb;
    
    -- 切换到 testdb 数据库
    USE testdb;
    
    -- 创建 users 表
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT NOT NULL
    );
    
    -- 创建 orders 表
    CREATE TABLE IF NOT EXISTS orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        amount DECIMAL(10, 2) NOT NULL,
        order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );
    
    -- 插入数据到 users 表
    INSERT INTO users (name, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 35),
    ('Da vid', 28);
    
    -- 插入数据到 orders 表
    INSERT INTO orders (user_id, amount) VALUES
    (1, 100.50),
    (2, 150.75),
    (3, 200.00),
    (1, 50.25);
    
    -- -- 查询所有用户信息
    -- SELECT * FROM users;
    
    -- -- 查询所有订单信息
    -- SELECT * FROM orders;
    
    -- -- 查询用户及其对应的订单信息(JOIN查询)
    -- SELECT users.name, users.age, orders.amount
    -- FROM users
    -- JOIN orders ON users.id = orders.user_id;
    
    -- -- 更新某个用户的数据,修改 Bob 的年龄
    -- UPDATE users SET age = 31 WHERE name = 'Bob';
    
    -- -- 删除 Charlie 用户及其对应的订单数据
    -- DELETE FROM orders WHERE user_id = (SELECT id FROM users WHERE name = 'Charlie');
    -- DELETE FROM users WHERE name = 'Charlie';
    
    -- -- 设置事务隔离级别为 REPEATABLE READ
    -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    补充知识点

    • MySQL 8.0+ 默认认证插件:caching_sha2_password,pymysql 不支持,得改成 mysql_native_password。
    • @'192.168.10.102' 限制了只允许该 Python 服务器连接,更安全。
    • 授权后必须执行 FLUSH PRIVILEGES 才能生效。

    三、Python 连接 MySQL 数据库

    创建文件 01_connect.py:

    import pymysql
    
    db = pymysql.connect(
        host="192.168.10.101",  #MySQL数据库的地址
        user="root",           #数据库的用户名
        password="pwd123",     #密码
        database="testdb",     #要连接的数据库的名称
        charset="utf8mb4"
    )         #创建数据库的连接
    
    cursor = db.cursor()   #创建游标对象
    
    cursor.execute("select * from users")   #执行SQL语句
    
    results=cursor.fetchall()
    for row in results:
        print(row)       #获取查询结果
    
    
    data = cursor.fetchone()
    print("MySQL 版本:", data)
    
    
    cursor.close()   #关闭游标连接
    db.close()     #关闭数据库连接

    补充知识点

    • host:必须写 MySQL 远程 IP,不能写 localhost。
    • charset=utf8mb4:支持中文和表情,生产环境的标准字符集。
    • cursor:执行 SQL、获取结果的对象。
    • 一定要记得关闭游标和连接,避免资源泄漏。

    执行命令:

    python3 01_connect.py

    四、常见的 MySQL 操作

    创建文件 02_operate.py(可以在01_connect.py的基础上改SQL语句,然后根据操作类型调整增删改查。改完后可以到MySQL上面查看效果):

    import pymysql
    
    db = pymysql.connect(
        host="192.168.10.101",
        user="root",
        password="pwd123",
        database="testdb",
        charset="utf8mb4"
    )
    cursor = db.cursor()
    
    # 创建表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        age INT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """)
    
    # 插入
    cursor.execute("INSERT INTO users(name, age) VALUES(%s, %s)", ("Alice", 25))
    db.commit()
    
    # 查询
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()
    for row in results:
        print(row)
    
    # 更新
    cursor.execute("UPDATE users SET age=%s WHERE name=%s", (26, "Alice"))
    db.commit()
    
    # 删除
    cursor.execute("DELETE FROM users WHERE name=%s", ("Alice",))
    db.commit()
    
    # 模糊查询
    cursor.execute("SELECT * FROM users WHERE name LIKE %s", ("%li%",))
    print("模糊查询:", cursor.fetchall())
    
    # 联合查询
    cursor.execute("""
    SELECT users.name, orders.amount
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
    """)
    print("联合查询:", cursor.fetchall())
    
    cursor.close()
    db.close()

    补充知识点

    • 增删改操作必须调用 commit(),否则不会写入数据库。
    • 使用 %s 占位符可以防止 SQL 注入。
    • fetchall() 获取全部结果,fetchone() 只获取一条。
    • LIKE %xxx% 实现全模糊匹配。
    • INNER JOIN 只返回匹配成功的记录。

    执行命令:

    python3 02_operate.py

    五、事务管理

    创建文件 03_transaction.py:

    import pymysql
    
    db = pymysql.connect(
        host="192.168.10.101",
        user="root",
        password="pwd123",
        database="testdb"
    )
    cursor = db.cursor()
    
    db.autocommit = False
    
    try:
        cursor.execute("INSERT INTO users(name, age) VALUES('Tom', 20)")
        cursor.execute("UPDATE users SET age=21 WHERE name='Tom'")
        db.commit()
        print("事务执行成功")
    except:
        db.rollback()
        print("事务已回滚")
    finally:
        cursor.close()
        db.close()

    补充知识点(ACID 完整)

    • 原子性(Atomicity):事务是最小单元,所有 SQL 要么全部成功,要么全部失败回滚。
    • 一致性(Consistency):事务前后数据完整性约束(主键、外键、非空)不被破坏。
    • 隔离性(Isolation):并发事务相互隔离,避免脏读、不可重复读、幻读。
    • 持久性(Durability):事务提交后,修改永久生效,即使数据库崩溃也不丢失。

    执行命令:

    python3 03_transaction.py

    六、MySQL 四种事务隔离级别

    在 MySQL 命令行中设置:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    补充知识点

    1. READ UNCOMMITTED:可读取未提交数据,容易产生脏读。
    2. READ COMMITTED:只能读已提交数据,解决了脏读。
    3. REPEATABLE READ:MySQL 默认级别,解决了脏读和不可重复读。
    4. SERIALIZABLE:最高级别,串行执行,不会出现并发问题,但性能最低。

    七、使用连接池

    1. 先安装相关软件:

    pip install dbutils

    2. 创建文件 04_pool.py:

    from dbutils.pooled_db import PooledDB
    import pymysql
    
    #数据库的连接配置
    pool = PooledDB(
        creator=pymysql,
        host="192.168.10.101",
        user="root",
        password="pwd123",
        database="testdb",
        charset="utf8mb4",
        maxconnections=10,
        mincached=2
    )     
      
    #创建连接池
    connection_pool=PooledDb(
        creator=pymysql,    #使用pymysql作为数据库连接库
        maxconnection=5,    #连接池中最大连接数
        **dbconfig
    )
    
    #获取连接
    db_connection=connection_pool.connection()
    cursor=db_connecction.cursor()
    
    cursor.execute("select * from users")
    results=cursor.fetchall()
    
    for row in results:
        print(row)
    
    cursor.close()
    db_connection.close()   #连接会自动归还给连接池

    补充知识点

    • 连接池预先创建连接,避免频繁创建/销毁,性能提升非常明显。
    • close() 是归还连接给池,不是关闭物理连接。
    • maxconnections:最大连接数,保护数据库不被压垮。
    • mincached:初始空闲连接数,提升启动速度。
    • 高并发项目必须使用连接池,这是基本功。

    执行命令:

    python3 04_pool.py

    总结

    从远程用户创建到Python连接、CRUD、事务、隔离级别,再到连接池,这套流程覆盖了日常开发中绝大部分场景。掌握了这些,基本就能应对大多数Python+MySQL的远程操作需求了。

    本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系bd@zhengruan.com
    作者最新文章
    编程开发 Python
    相关文章 更多
    python删除一个列表里的重复元素的方法与代码示例
    python删除一个列表里的重复元素的方法与代码示例

    学习如何使用Python高效删除列表中的重复元素。本文提供5种常见方法的代码示例,涵盖保持顺序与不保持顺序的场景,适合初学者和进阶开发者参考。

    Python写接口的步骤及完整代码示例
    Python写接口的步骤及完整代码示例

    详细讲解使用Python编写Web接口的核心步骤与完整代码示例,涵盖环境准备、路由定义、数据校验、服务启动及调试测试方法。

    codex怎么读取word文档及docx文件操作教程
    codex怎么读取word文档及docx文件操作教程

    介绍如何利用编程工具读取和解析Word文档(docx格式),涵盖基础文本提取代码示例、结构化数据处理逻辑,以及处理复杂排版时的技术局限与替代方案。

    怎么在Python中调用C语言代码提升性能
    怎么在Python中调用C语言代码提升性能

    详细讲解在Python中调用C语言代码提升性能的完整实践指南,包含原理分析、编译步骤、ctypes与CFFI具体实现、性能对比及常见陷阱。

    Python与C语言的性能差异及底层原理
    Python与C语言的性能差异及底层原理

    深入解析Python与C语言在执行效率上的数量级差异,从编译机制、变量内存布局、动态类型推断到GIL锁,系统阐明底层核心原理。

    Python和C语言哪个更适合编程入门
    Python和C语言哪个更适合编程入门

    客观对比Python与C语言在心智负担、底层机制与学习反馈上的核心差异,提供清晰有据的选择标准与切实的入门学习路径。

    selenium上传文件教程本地文件路径设置方法
    selenium上传文件教程本地文件路径设置方法

    深入解析Selenium处理文件上传的技术细节,涵盖标准HTML控件的路径直接注入法与非标准控件的系统级模拟方案,提供Windows与Linux环境下的路径设置最佳实践及常见异常排查。

    大数据开发语言选型:Java对比Scala与Python的优缺点及适用场景
    大数据开发语言选型:Java对比Scala与Python的优缺点及适用场景

    针对大数据工程场景,系统对比Java、Scala与Python在执行性能、生态结合、开发效率和维护成本上的核心差异,给出清晰的架构选型依据与落地方案。

    langgraph python guide中文入门教程详解
    langgraph python guide中文入门教程详解

    本篇LangGraph Python中文入门教程详解,系统梳理StateGraph状态机定义、节点编写、边与条件路由、检查点持久化(MemorySaver)等核心机制,提供从环境安装到完整循环智能体构建的代码示例与实操指导。

    Windows 安装 Python:从官方入口到虚拟环境的可复现实践
    Windows 安装 Python:从官方入口到虚拟环境的可复现实践

    面向后端开发者,基于 Python 官方文档说明如何在 Windows 上安装 Python、创建 venv 虚拟环境及使用 pip 管理依赖。包含可复制命令、验证方法及工具选择边界,避免常见配置误区。

    查看更多
    精品专题 更多
    装机必备
    装机必备

    正软商城装机必备专区,精选办公、浏览器、安全防护、影音播放、压缩解压、设计创作和系统工具等电脑常用正版软件,帮助用户快速完成新电脑软件配置。

    Windows
    Windows

    正软商城Windows软件专区,汇集适用于Windows电脑的办公、设计、安全防护、影音播放、开发工具和系统优化软件,提供软件介绍、系统要求、正版授权及购买下载服务。

    PDF教程
    PDF教程

    正软商城PDF教程频道提供PDF编辑、转换、合并、拆分、压缩及格式处理方法,同时介绍常用PDF软件和工具的使用技巧。

    Mac软件 更多
    讯飞智作 网页版
    讯飞智作 网页版
    Windows/ macOS/ Linux

    讯飞智作是一款基于语音合成技术、虚拟人驱动技术与数字人形象声音构建能力的 AI 数字人音视频创作平台。

    火山引擎 网页版
    火山引擎 网页版
    Windows/ macOS/ Linux

    火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力,依托字节自研豆包多模态大模型,凭借火山方舟一站式平台,提供模型精调、推理部署、智能应用搭建等全链路服务,结合高性能AI算力,助力各行业快速落地大模型数字化与智能化应用。

    Aperty macOS版
    Aperty macOS版
    macOS

    Aperty 是 Skylum 推出的 AI 人像修图软件,支持自然肤质修饰、瑕疵处理、面部与身体调整、预设及批量同步。

    WINDOWS 更多
    讯飞智作 网页版
    讯飞智作 网页版
    Windows/ macOS/ Linux

    讯飞智作是一款基于语音合成技术、虚拟人驱动技术与数字人形象声音构建能力的 AI 数字人音视频创作平台。

    火山引擎 网页版
    火山引擎 网页版
    Windows/ macOS/ Linux

    火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力,依托字节自研豆包多模态大模型,凭借火山方舟一站式平台,提供模型精调、推理部署、智能应用搭建等全链路服务,结合高性能AI算力,助力各行业快速落地大模型数字化与智能化应用。

    PixTV 网页版
    PixTV 网页版
    Windows

    PixTV 是一款在浏览器中使用的 AI 视觉内容创作平台,提供图片、视频、音频、文字生成、AI 分镜、局部重绘、提示词反推和无限画布等工具,适合短剧、漫剧、广告及社交媒体内容制作。