mysql数据库连接池python

柔情只为你懂 2022-07-14 08:12 307阅读 0赞

## 1. DBUtils ##

[官方文档][Link 1]还是11年的，但还是非常简单好用。主要使用其中的PersistentDB 和 PooledDB两个模块。

### 1. PersistentDB ###

每当线程第一次打开数据库连接时，PersistentDB将建立一个与该数据库的新连接，该连接将始终用于此特定线程，不能被其他线程使用。当该线程调用close()后，该连接回到线程池，静静的等待这个线程再次调用它（其他线程无法使用这个连接）。该连接最终随该线程死亡而真正关闭。

### 2. PooledDB ###

@singleton
    class TlcfDBconPool(object):
        def __init__(self):
            db_conf_dict = InternalCFG().get_datayesdb_conf
            self.pool = PersistentDB.PersistentDB(creator=MySQLdb, maxusage=5,host=db_conf_dict['host'], user=db_conf_dict['user'], passwd=db_conf_dict['passwd'],db=db_conf_dict['db'], port=db_conf_dict['port'])
    
        def get_db_connection(self):
            return self.pool.connection()

## 2. SqlAlchemy中的连接池 ##

作为高级的ORM工具，SqlAlchemy本身就有连接池的

首先engine.connect()返回的是Connection object.

> Return a new Connection object.

> The Connection object is a facade that uses a DBAPI connection internally in order to communicate with the database. This connection is procured from the connection-holding Pool referenced by this Engine. When the close() method of the Connection object is called, the underlying DBAPI connection is then returned to the connection pool, where it may be used again in a subsequent call to connect().

engine.raw\_connection()返回的是 Raw DBAPI Connections，但是这个依然不是我想要的东西，因为我在代码中依然发现这个connection是“proxied”的,每次返回的object的id都不一样

The name “fairy” is inspired by the fact that the \_ConnectionFairy object’s lifespan is transitory, as it lasts only for the length of a specific DBAPI connection being checked out from the pool, and additionally that as a transparent proxy, it is mostly invisible.

engine = DBConnectionFactory().get_db_connection('db')
    conn = engine.connect()
    print conn.connection.thread_id()
    print id(conn)
    sql = "select * from mytable where trade_date={2}"
    dat = read_db(conn, sql)
    dat.columns =['ID', 'factor']

[Link 1]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html