暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / python 连接 mysql 的最佳尝试

python 连接 mysql 的最佳尝试

鸡仔说 2021-05-26
1262

鸡仔最近在开发的过程中经常会遇到连接 mysql 数据库的场景，一开始通过 pymysql 直接连接数据库，项目比较简单的时候，还蛮好用的。但随着时间的推移，连接数据库的场景变得复杂起来。比如 有些场景需要对 mysql 进行频繁连接；又比如某些时候需要进行跨 db 操作数据库。

基于此，鸡仔觉得有必要对 python + mysql 的连接做一个调研。寻寻觅觅几多年鸡仔终于发现了一个好用的工具 DBUtils，可以轻松实现对数据库连接池的管理。其原理实现如图所示


其中 pooled_db 模块能够实现多线程管理，大家可以根据自己的需要配置，鸡仔通过阅读文档实现了满足目前业务需求的代码，都加了注释的，方便大家看。

代码地址：https://github.com/hacksman/learn_lab/blob/master/small_bug_lab/mysql_pool_conn.py

在实现上，鸡仔用 mysql 连接时使用了单例模式，防止项目中出现大量不必要的 mysql 实例，单例的实现通过字典实现，其中的 key 是 mysql 的基础配置（host、port 等）。这样做的好处是，在项目中能够轻松实现跨 db 操作数据库

下面鸡仔带大家跑一下简单的测试用例
if __name__ == "__main__":
    local = {
        "host": "127.0.0.1",
        "port": 3306,
        "db": "test_demo",
        "user": "root",
        "password": "your mysql password here"
    }

    # 测试查看在最小缓存初始化缓存
    local_mysql = MysqlClient(**local)
    time.sleep(5)

复制

首先，替换上面的配置，使用你自己的数据库配置，我们先测试一下最小数据库缓存，也就是初始化缓存的连接数。执行上面的代码后，在休眠的 5s 内，去数据库中，用命令行查看当前数据库的连接数。
mysql> show processlist;
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
| Id  | User            | Host            | db        | Command | Time    | State                  | Info             |
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
|   4 | event_scheduler | localhost       | NULL      | Daemon  | 2247450 | Waiting on empty queue | NULL             |
| 660 | root            | localhost       | NULL      | Query   |       0 | starting               | show processlist |
| 661 | root            | localhost:63148 | test_demo | Sleep   |       2 |                        | NULL             |
| 662 | root            | localhost:63149 | test_demo | Sleep   |       2 |                        | NULL             |
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
4 rows in set (0.00 sec)

复制

我这边设置的最小连接缓存数是 2，执行结果符合预期。

# 测试查看在最小缓存初始化缓存
local_mysql = MysqlClient(**local)
# time.sleep(5)
# 测试查看单例模式
local_mysql_again = MysqlClient(**local)
time.sleep(5)

复制

接下来测试单例模式，再实例化一个 mysql 连接对象
mysql> show processlist;
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
| Id  | User            | Host            | db        | Command | Time    | State                  | Info             |
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
|   4 | event_scheduler | localhost       | NULL      | Daemon  | 2247638 | Waiting on empty queue | NULL             |
| 660 | root            | localhost       | NULL      | Query   |       0 | starting               | show processlist |
| 663 | root            | localhost:63709 | test_demo | Sleep   |       2 |                        | NULL             |
| 664 | root            | localhost:63710 | test_demo | Sleep   |       2 |                        | NULL             |
+-----+-----------------+-----------------+-----------+---------+---------+------------------------+------------------+
4 rows in set (0.00 sec)

复制

执行结果和上面一样，说明没有重复创建实例对象

# 测试 insert many
values = list(map(lambda x: (f"小姐姐{x}号", x, "女", datetime.datetime.now(), datetime.datetime.now()), range(10)))
print(values)
insert_many_sql = 'INSERT INTO `anchor` values (%s, %s, %s, %s, %s)'
insert_count = local_mysql.execute_many(insert_many_sql, values)
print(f"总共插入「{insert_count}」条数据")

复制

创建一个 anchor 表，用来存达人的基础信息，定义好主播的 nickname, age, gender, _create_time, _update_time。然后执行上面的 execute_many 操作。输出如下

[('小姐姐0号', 0, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985140), datetime.datetime(2021, 3, 16, 8, 29, 20, 985144)), ('小姐姐1号', 1, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985145), datetime.datetime(2021, 3, 16, 8, 29, 20, 985145)), ('小姐姐2号', 2, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985146), datetime.datetime(2021, 3, 16, 8, 29, 20, 985147)), ('小姐姐3号', 3, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985148), datetime.datetime(2021, 3, 16, 8, 29, 20, 985148)), ('小姐姐4号', 4, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985149), datetime.datetime(2021, 3, 16, 8, 29, 20, 985149)), ('小姐姐5号', 5, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985150), datetime.datetime(2021, 3, 16, 8, 29, 20, 985151)), ('小姐姐6号', 6, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985151), datetime.datetime(2021, 3, 16, 8, 29, 20, 985152)), ('小姐姐7号', 7, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985152), datetime.datetime(2021, 3, 16, 8, 29, 20, 985153)), ('小姐姐8号', 8, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985154), datetime.datetime(2021, 3, 16, 8, 29, 20, 985154)), ('小姐姐9号', 9, '女', datetime.datetime(2021, 3, 16, 8, 29, 20, 985155), datetime.datetime(2021, 3, 16, 8, 29, 20, 985155))]
总共插入「10」条数据

复制

完了之后，测试查询操作
# 测试 select many
select_many = 'SELECT * FROM `anchor`'
for item in local_mysql.select_many(select_many):
    print('select_many 数据>>>>', item)

复制

输出结果如下所示，注意一下，select_many 返回的是迭代器，防止一次性读大量的数据造成内存被撑爆。select_one 就没有这个问题，所以是直接返回结果。

select_many 数据>>>> {'nickname': '小姐姐0号', 'age': 0, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐1号', 'age': 1, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐2号', 'age': 2, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐3号', 'age': 3, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐4号', 'age': 4, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐5号', 'age': 5, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐6号', 'age': 6, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐7号', 'age': 7, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐8号', 'age': 8, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}
select_many 数据>>>> {'nickname': '小姐姐9号', 'age': 9, 'gender': '女', '_create_time': '2021-03-16 08:29:21', '_update_time': '2021-03-16 08:29:21'}

复制

接下来测试不存在的数据
# 测试查询不存在的数据
select_not_exist = "SELECT * FROM `anchor` WHERE nickname=%s"
not_exist_result = local_mysql.select_one(select_not_exist, "鸡仔说")
print(f"not exist select one 数据>>>{not_exist_result}")

复制

结果如下
not exist select one 数据>>>None

复制

最后也是最重要的，测试一下事物操作
    # 测试事务操作
    sql_insert_one = 'INSERT INTO `anchor` value (%s, %s, %s, %s, %s)'
    insert_one_value = ("小姐姐10号", 10, "女", datetime.datetime.now(), datetime.datetime.now())
    local_mysql.begin()
    local_mysql.execute(sql_insert_one, insert_one_value)
    print(f"事务测试，已经执行了插入操作，快去数据库查看一下，目前数据应该还没有执行，等待确认操作...")
    time.sleep(10)
    print(f"即将执行确认操作")
    time.sleep(1)
    local_mysql.end(succeed=True)
    print(f"已确认执行操作，请再次查看数据库...")

复制

插入第十一条数据，也就是小姐姐10号，当代码执行到第一行日志时，去数据库查看一下，此时因为事务操作没有被确认，因此数据库内还没有数据，数据库内数据如下所示

+---------------+------+--------+---------------------+---------------------+
| nickname      | age  | gender | _create_time        | _update_time        |
+---------------+------+--------+---------------------+---------------------+
| 小姐姐0号     |    0 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐1号     |    1 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐2号     |    2 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐3号     |    3 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐4号     |    4 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐5号     |    5 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐6号     |    6 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐7号     |    7 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐8号     |    8 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐9号     |    9 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
+---------------+------+--------+---------------------+---------------------+

复制

当事务操作被确认之后，数据库内数据就会增加，在查询一下数据库
+----------------+------+--------+---------------------+---------------------+
| nickname       | age  | gender | _create_time        | _update_time        |
+----------------+------+--------+---------------------+---------------------+
| 小姐姐0号      |    0 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐1号      |    1 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐2号      |    2 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐3号      |    3 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐4号      |    4 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐5号      |    5 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐6号      |    6 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐7号      |    7 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐8号      |    8 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐9号      |    9 | 女     | 2021-03-16 08:29:21 | 2021-03-16 08:29:21 |
| 小姐姐10号     |   10 | 女     | 2021-03-16 08:43:44 | 2021-03-16 08:43:44 |
+----------------+------+--------+---------------------+---------------------+

复制

以上，就是鸡仔对于 python 连接 mysql 的经验总结和应用。除了以上的场景，DBUtils 还提供 PersistentDB 连接模式，该模式下线程之间的链接不共享，小伙伴们可以去官方文档看下尝试一下。就酱~摸鱼去啦~

参考资料：

[1] Vishal (2021). Python Database Connection Pooling with MySQL

https://pynative.com/python-database-connection-pooling-with-mysql/

[2] 龙潭斋 (2019). python 数据库连接池 DBUtils 源码解析.

https://techlog.cn/article/list/10183379

[3] 洛辰 (2018). Python3+pymyql+DBUtils实现数据库连接池.

https://www.songbin.top/view/282

[4] 老刘 (2019). MySQL数据库连接的相关探索.

https://zhuanlan.zhihu.com/p/87383437

[5] Rooky1993 (2019). MySQL数据库连接数.

https://www.jianshu.com/p/f6b8942702c0

[6] Cito (2017). DButils Documentation.

https://webwareforpython.github.io/DBUtils/main.html





以上，如果觉得内容对你有所帮助，还请点个「在看」支持，谢谢各位dai佬！




好看的人都点了在看


mysqlpython
文章转载自鸡仔说，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论