Python database connection Pool module-----dbutils use

Python's database connection pool implementation----Dbutils

Dbutils is a database connection pool implementation module belonging to the Webware project, which is used to thread database connections to enable secure and efficient access to the database's modules

Dbutils is actually a Python package with two submodules, one for connecting the Db-api 2 module and the other for connecting a typical Pygresql module.

Global DB-API 2 variables
steadydb.py	For stabilizing database connections
pooleddb.py	Connection pool
persistentdb.py	Maintain a persistent database connection
simplepooleddb.py	Simple Connection Pool

A typical pygresql variable
steadypg.py	Stable Pygresql Connection
pooledpg.py	Pygresql Connection Pool
persistentpg.py	Maintain a continuous pygresql connection
simplepooledpg.py	Simple Pygresql Connection Pool

The dependency on the standard Db-api 2 module is as follows:

The dependency on the typical Pygresql module is as follows:

Simplepooleddb

Dbutils.simplepooleddb is a very simple database connection pooling implementation. He lacks many features than the perfect pooleddb module. Dbutils.simplepooleddb is essentially similar to the Miscutils.dbpool part of this webware. You can think of it as a demo program.

Steadydb

dbutils.steadydb is a module that implements a "tough" database connection, based on the original connection established by Db-api 2. A "tough" connection means reconnecting when the connection is closed or when the usage limit is limited.

A typical example is when the database restarts and your program is still running and requires access to the database, or when your program is connected to a remote database behind a firewall and the firewall is restarted with a status of missing.

Generally you don't need to use steadydb directly, it just provides basic services to the next two modules, persistentdb and pooleddb .

Persistentdb

Dbutils.persistentdb implements a tough, thread-safe, stubborn database connection using the Db-api 2 module. As shown in the connection layer steps when using persistentdb :

When a thread first opens a database connection, a connection is opened and used only by that thread. When the thread closes the connection, the connection continues to open for the next request by this thread to use the already open connection. The connection is automatically closed when the thread dies.

Simply put, persistentdb attempts to reuse the database connection to improve the database access performance of the threaded program, and he ensures that the connection is not shared between threads.

As a result, Persistentdb can work well when the underlying DB-API module is not thread-safe, and he will also avoid problems when other threads change the database session or use multi-statement transactions.

Pooleddb

Dbutils.pooleddb implements a strong, thread-safe, cached, reusable database connection using any DB-API 2 module. As shown in the workflow when using pooleddb :

pooleddb can share open database connections between different threads. This is when you connect and specify the maxshared parameter, and the underlying DB-API 2 interface is thread safe, but you can still use a private database connection instead of sharing connections between threads. In addition to shared connections, you can set up a connection pool of at least mincached and allow up to maxcached connections, which can be used for both private and shared connection pools. When a thread closes a non-shared connection, it is returned to the free connection pool for the next use.

If the underlying DB-API module is non-thread-safe, the thread lock ensures that the use of pooleddb is thread-safe. So you don't have to worry about this, but you have to be careful when you use a private connection to alter a database session or execute a multi-command transaction.

Which one should I choose?

Both persistentdb and pooleddb are designed to reuse database connections to improve performance and to keep the database stable.

So choose which module, you can refer to the above explanation. Persistentdb will maintain a certain number of connections for frequent use. In this case you always keep a fixed number of connections. If your program frequently starts and shuts down threads, it is best to use pooleddb . Better tuning will be mentioned later, especially when using the thread-safe Db-api 2 module.

Of course, the interfaces of the two modules are very similar, and you can easily convert between them and see which one is better.

How to use

All modules are used in a similar way, but there are some differences in initializing "pooled" and "persistent", especially between Db-api and Pygresql.

Only Persistentdb and more complex POOLEDDB modules are explained here. For details of other modules, please participate in their documentation. Using the Python interpreter console, you can display the pooleddb documentation as follows:

Help (POOLEDDB)

Persistentdb

In order to use Persistentdb you first need to set up a generator for a particular database connection by creating an instance of Persistentdb , with the following parameters in the bed bottom:

• Creator: You can use any function that returns DB-API 2 Connection object to live DB-API 2 compatible database module.
• maxusage: The maximum number of times a connection is allowed to be reused (the default is 0 or False means infinite reuse), and when the limit is reached, the database is reconnected
• setsession: An optional list of SQL commands can be used to prepare the session, such as ["Set Datestyle to German", ...]
• The creator function, or a function that generates a DB-API 2 connection, can accept additional parameters, such as hostname, database, user name, password, and so on. You can also choose other parameters to pass to creator and allow for failed re-connection and load balancing.

For example, if you are using pgdb as a database module and want to connect to a native database mydb , allow 1000 times for reuse:

Import pgdb # import used Db-api 2 modulefrom dbutils.persistentdb Import persistentdbpersist = Persistentdb (pgdb, +, D Atabase= ' MyDB ')

After the connection builder has been completed as set up above, you can request a connection as follows:

db = Persist.connection ()

You can use these connections just as you would with the original Db-api 2 connection. Actually what you get is a tough connection through ' Steadydb ', based on Db-api 2.

Close a hard connection using db.close () , which is actually ignored internally and for the next use. When a thread is closed, the database connection is also automatically closed. You can change this behavior by persist._closeable to True .

Pooleddb

In order to use the pooleddb module, you first need to set up the database connection pool by creating pooleddb , passing the following parameters:

• Creator : You can generate any function that  db-api 2  connected or  db-api a 2 -compatible database connection module.
• mincached  : Number of empty connections opened at startup (default value   0   means no connection is created at start)
• maxcached : The maximum number of connections used by the connection pool (default   0   No limit connection pool size)
• maxshared : Maximum number of shared connections allowed (default value   0   on behalf of all connections are private) if the maximum number is reached, the connection requested for the share will be used for sharing.
• maxconnections : Maximum allowable number of connections (default   0   Representative not limited)
• blocking : Set the behavior when the maximum number is reached (default value   0   or   False   returns an error; others are blocked until the number of connections is reduced)
• maxusage : Maximum number of allowed multiplexing times for a single connection (default   0   or   False   for unrestricted reuse). When the maximum value is reached, the connection is automatically reconnected (closed and reopened)
• setsession : An optional list of SQL commands is used to prepare each session, such as   ["Set datestyle  to german ",  ..]
The
• creator   function or a function that can generate a connection can accept other parameters passed in here, such as host name, database, user name, password, and so on. You can also choose to pass in additional parameters to the creator function, allowing for failed re-connection and load balancing.

For example, if you are using pgdb as the Db-api module, and you want to have at least 5 connections to the database in the connection pool mydb

Import pgdb # import used Db-api 2 modulefrom dbutils.pooleddb Import pooleddbpool = Pooleddb (Pgdb, 5, database= ' MyDB ')

Once the connection pool is set up, you can request a connection as follows:

db = Pool.connection ()

You can use these connections like the original Db-api 2. The actual use of the "Steadydb" version of the strong connection.

Note that the connection can be shared with other threads as long as you set the maxshared parameter to nonzero, and the Db-api 2 module is also allowed. If you want to use a private connection then use:

db = pool.connection (0)

If you no longer need this connection, you can return to the connection pool using db.close () . You can also use the same method to get another connection.

Warning: in a multithreaded environment, do not use the following method:

Pool.connection (). cursor (). Execute (...)

This will result in premature release of the connection for reuse and, if non-thread-safe, error. Ensure that the connection object is always present during your use, for example:

db = Pool.connection () cur = db.cursor () cur.execute (...) res = Cur.fetchone () cur.close () # or Del curdb.close () # or Del db