# -*- coding: utf-8 -*-
# __author__ = 'zhourudong'
# 连接池的实现
import pymysql
import Queue
from pymysql.cursors import DictCursor


class ConnectionPool:
    def __init__(self, **kwargs):
        self.size = kwargs.pop('size', 10)  # 允许最大的连接
        self.idle = kwargs.pop('idle', 3)  # 空闲连接
        self.kwargs = kwargs
        self.length = 0  # 已用连接
        self.connections = Queue.Queue(maxsize=self.idle)

    def _connect(self):  # 创建连接的函数
        if not self.connections.full():  # 如果当前连接小于预留的连接则创建
            conn = pymysql.connect(**self.kwargs)
            self.connections.put(conn)  # 放到池中
            self.length += 1
        else:
            raise RuntimeError('lot of connections ')

    def _close(self, conn):
        conn.close()  #
        self.length -= 1  # 17分钟

    def get(self, timeout=None):
        if self.connections.empty() and self.length < self.size:  # 如果连接池为空 或者已用连接小于设置连接数上限
            self._connect()
        return self.connections.get(timeout=timeout)

    def return_resource(self, conn):
        if self.connections.full():
            # 如果连接池中剩余的连接大于 设置连接上线)(连接池满则关闭, 否则把连接放到连接池中)
            self._close(conn)
            return
        self.connections.put(conn)


if __name__ == '__main__':
    pool = ConnectionPool(host='127.0.0.1',
                          port=23316, user='root',
                          password='123456',
                          database='zrd',
                          cursorclass=DictCursor)
    for _ in range(11):
        conn = pool.get()
        conn.cursor()
    # conn = pool.get()
    # try:
    #
    #     with conn as cur:
    #         cur.execute(""" SELECT * FROM `user`""")
    #         for res in cur.fetchall():
    #             print res
    # finally:
    #     pool.return_resource(conn)

# -*- coding: utf-8 -*-
# __author__ = 'zhourudong'
# 连接池的实现
import pymysql
import Queue
from pymysql.cursors import DictCursor
from contextlib import contextmanager

class ConnectionPool:
    def __init__(self, **kwargs):
        self.size = kwargs.pop('size', 10)  # 允许最大的连接
        self.idle = kwargs.pop('idle', 3)  # 空闲连接
        self.kwargs = kwargs
        self.length = 0  # 已用连接
        self.connections = Queue.Queue(maxsize=self.idle)

    def _connect(self):  # 创建连接的函数
        if not self.connections.full():  # 如果当前连接小于预留的连接则创建
            conn = pymysql.connect(**self.kwargs)
            self.connections.put(conn)  # 放到池中
            self.length += 1
        else:
            raise RuntimeError('lot of connections ')

    def _close(self, conn):
        conn.close()  #
        self.length -= 1  # 17分钟

    def get(self, timeout=None):
        if self.connections.empty() and self.length < self.size:  # 如果连接池为空 或者已用连接小于设置连接数上限
            self._connect()
        conn =  self.connections.get(timeout=timeout)
        conn.ping(reconnect=True) # 检查可用性
        return conn

    def return_resource(self, conn):
        if self.connections.full():
            # 如果连接池中剩余的连接大于 设置连接上线)(连接池满则关闭, 否则把连接放到连接池中)
            self._close(conn)
            return
        self.connections.put(conn)

    # 引入上下文管理
    @contextmanager
    def __call__(self, timeout=None):
        conn = self.get(timeout)
        try:
            yield conn.cursor()  # 直接返回游标对象
            conn.commit()
        except:
            conn.rollback()
        finally:
            self.return_resource(conn)

if __name__ == '__main__':
    pool = ConnectionPool(host='127.0.0.1',
                          port=23316, user='root',
                          password='123456',
                          database='zrd',
                          cursorclass=DictCursor)
    # with pool() as conn:
    with pool() as cur:
        cur.execute(""" SELECT * FROM `user`""")
        for res in cur.fetchall():
            print res

#!/usr/bin/env python
# -- coding: utf-8 --
# Time: 2017/2/28 12:03
# Author: zhourudong




import queue
from pymysql.cursors import DictCursor
from contextlib import contextmanager
import pymysql


class ConnectionPool:
    def __init__(self, **kwargs):
        self.size = kwargs.pop('size', 10)  # 允许最大的连接
        self.idle = kwargs.pop('idle', 3)  # 空闲连接
        self.kwargs = kwargs
        self.length = 0  # 已用连接
        self.connctions = queue.Queue(maxsize=self.idle)

    def _connect(self):
        if not self.connctions.full():  # 如果当前连接小于预留的连接则创建
            conn = pymysql.connect(**self.kwargs)
            self.connctions.put(conn)  # 放到连接池中
            self.length += 1
        else:
            raise RuntimeError('lot of connections')

    def _close(self, conn):
        conn.close()
        self.length -= 1

    def get(self, timeout=None):
        if self.connctions.empty() and self.length < self.size:  # 如果连接池为空 或者已用连接小于设置连接数上限
            self._connect()  # 则创建连接
        conn = self.connctions.get(timeout=timeout)
        conn.ping(reconnect=True)
        return conn

    def return_resource(self, conn):
        if self.connctions.full():
            # 如果连接池中剩余的连接大于 设置连接上线)(连接池满则关闭, 否则把连接放到连接池中)
            self._close(conn)
            return
        self.connctions.put(conn)

    @contextmanager
    def __call__(self, timeout=None):
        conn = self.get(timeout)
        try:
            yield conn.cursor()
            conn.commit()
        except:
            conn.rollback()
        finally:
            self.return_resource(conn)


if __name__ == '__main__':
    pool = ConnectionPool(host='127.0.0.1',
                          port=23316, user='root',
                          password='123456',
                          database='zrd',
                          cursorclass=DictCursor)
    # with pool() as conn:
    with pool() as cur:
        cur.execute(""" SELECT * FROM `user`""")
        re_dict = cur.fetchall()
        print(re_dict)
        # for res in re_dict:
        #     print(res)