专栏首页Python中文社区Python中文社区开源项目计划:pymysql-connpool

Python中文社区开源项目计划:pymysql-connpool

A simple connection pool based PyMySQL. Mainly focus on multi threads or async mode when use pymysql, but also compatible with single thread mode for convenience when you need to use these two mode together. Within multi threads mode support the multiplexing similar feature(when use connection with Context Manager Protocol).

Problem: When use pymysql with python multi threads, generally we will face the questions:

1、It can't share a connection created by main thread with all sub-threads. It will result error like this: pymysql.err.InternalError: Packet sequence number wrong - got 0 expected 1

2、If we make every sub-thread to create a connection and close it when this sub-thread end, that's workable but obviously lead to high cost on establish connections with MySQL.

So I implement this module aimed at create as least connections as possible with MySQL in multi-threads programing.

This module contain two class:

  • Connection is a subclass of pymysql.connections.Connection, it can use with or without connectionpool, It's usage is all the same with pymysql. The detail(when with connectionpool, it should take additional action to maintain the pool) implement about connection pool is hiddened. This class provide a wrapped execute_query() method for convenience, which take several parameters.
  • ConnectionPool's instance represent the real connection_pool.

Use example

multi-threads mode:

The mainly difference with single-thread mode is that we should maintain the status of the pool. Such as 'get connection from pool' or 'put connection back to pool', in which case there are also some case to deal, such as:

  • when get connection from a pool: we should deal with the timeout and retry parameters
  • when put connection back to pool: if we executed queries without exceptions, this connection can go back to pool directly; but if exception occurred, we should decided whether this connection should go back to pool depend on if it is reusable(base on the exception type). If the connection shouldn't bo back to pool, we close it and recreate a new connection then put it to the pool.

Luckily, this module will take care of these complicated details for you automatic.

There also can create more than one connection_pool(with distinct ConnectionPool.name attribute) to associate with different databases.

In the example below, we will see how it work within connection_pool feature:

>>> import pymysql_pool
>>> pymysql_pool.logger.setLevel('DEBUG')
>>> config={'host':'xxxx', 'user':'xxx', 'password':'xxx', 'database':'xxx', 'antocomit':True}

### Create a connection pool with 2 connection in it
>>> pool1 = pymysql_pool.ConnectionPool(size=2, name='pool1', **config)
>>> pool1.size()
2
>>> con1 = pool1.get_connection()
2017-12-25 21:38:48    DEBUG: Get connection from pool(pool1)
>>> con2 = pool1.get_connection()
2017-12-25 21:38:51    DEBUG: Get connection from pool(pool1)
>>> pool1.size()
0

### We can prophesy that here will occur some exception, because the pool1 is empty
>>> con3 = pool1.get_connection(timeout=0, retry_num=0)
Traceback (most recent call last):
  File "e:\github\pymysql-connpool\pymysql_pool.py", line 115, in get_connection
    conn = self._pool.get(timeout=timeout) if timeout > 0 else self._pool.get_nowait()
queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<pyshell#37>", line 1, in <module>
    con3 = pool1.get_connection(timeout=0, retry_num=0)
  File "e:\github\pymysql-connpool\pymysql_pool.py", line 128, in get_connection
    self.name, timeout, total_times))
pymysql_pool.GetConnectionFromPoolError: can't get connection from pool(pool1) within 0*1 second(s)

### Now let's see the connection's behavior when call close() method and use with Context Manager Protocol
>>> con1.close()
2017-12-25 21:39:56    DEBUG: Put connection back to pool(pool1)
>>> with con1 as cur:
    cur.execute('select 1+1')

1
2017-12-25 21:40:25    DEBUG: Put connection back to pool(pool1)
### We can see that the module maintain the pool appropriate when(and only when) we call the close() method or use the Context Manager Protocol of connection object.

NOTE 1: We should always use one of the close() method or Context Manager Protocol of connection object, otherwise the pool will exhaust soon.

NOTE 2: The Context Manager Protocol is preferred, it can achieve the "multiplexing" similar effect.

NOTE 3: When use close() method, take care never use a connection object's close() method more than one time(you know why~).

作者:jkklee,6年运维老司机一枚,擅长高并发及复杂场景下的故障排查和性能优化。目前比较侧重于将自己的运维积累转化成通用易用的各种工具,希望能帮到更多的运维同胞。

GitHub:https://github.com/jkklee/pymysql-connpool

往期开源项目介绍

Python开源项目介绍:用zmail简单地发邮件

Python开源项目介绍:网站日志分析工具

Python中文社区开源项目计划:ImagePy

本文分享自微信公众号 - Python中文社区(python-china)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一文读懂Python装饰器由来(一)

    Python装饰器是非常不错的特性,熟练掌握装饰器会让你的编程思路更加宽广,程序也更加pythonic。下面就让我们一起来探讨一下python的装饰器吧。

    Python中文社区
  • 摩拜单车爬虫源码及解析

    專 欄 ❈我是思聪,Python中文社区专栏作者 博客: http://www.jianshu.com/u/b1e713e56ea6❈ 为什么爬摩拜的数据 ...

    Python中文社区
  • 深度剖析为什么Python中整型不会溢出

    在python2时代,整型有 int 类型和 long 长整型,长整型不存在溢出问题,即可以存放任意大小的整数。在python3后,统一使用了长整型。这也是吸引...

    Python中文社区
  • 第一周作业-模拟登陆

    1 #coding:utf-8 2 #Author:Mr Zhi 3 count = 0 #为累计密码输入次数,赋值初始值 4 User_i...

    用户1679793
  • 关于第三方库的想法

    1、消耗时间,一个开源库,拿过来需要仔细考察代码质量,确认是否足够可靠。如果出现问题,需要仔细审查开源库的内部实现。如果这些工作太消耗时间,还不如自己实现。

    用户1451823
  • MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决方法

    利用:from sqlalchemy import create_engine 链接数据库的过程中报错(数据量大时) sqlalchemy.exc.Op...

    学到老
  • MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决方法

    利用:from sqlalchemy import create_engine 链接数据库的过程中报错(数据量大时) sqlalchemy.exc.Op...

    学到老
  • 公共密钥基础设施迁往公有云安全吗?

    把诸如公共密钥基础设施这样的安全控制措施迁往云计算确实是可以实现成本节省的,但是云计算的安全措施是否强大到足以确保其安全性呢? 当你在考虑一个基于PKI的基本系...

    静一
  • SaaS编年史之技术视角观察

    围绕一个数据库分成前台应用和管理后台, 而这里的管理后台其实就是后来声称是SaaS的大多数应用平台的雏形。

    扶墙老师
  • 微信PC端技术研究(3)-如何找到消息发送接口

    前一篇()已经说过 CE 是什么,也应用 CE 研究了如何保存微信语音,这篇继续使用 CE 和 OD 来研究一下微信的消息发送接口。

    信安之路

扫码关注云+社区

领取腾讯云代金券