PyMysqlPool

Table of Contents

Desc

python practical mysql pool desc: This package contains a pure-python mysql connector library. The goal of PyMysqlPool is to be a mysql pool and motivation from=>[lost connection to MySQL server during query] base on mysql-connector .

feature

• easy to use.
• support 【no、fixed 、dynamic pool】.
• manage 【fail/lost connection】.
• support 【no、fixed 、dynamic pool】=>Django framework.
• support 【no、fixed 、dynamic pool】=>Flask framework.

Requirements

• Python lib -- one of the following:

  MySQLdb

• Python -- one of the following:

  success test in python >=2.7

• MySQL Server -- one of the following:

  MySQL >= 5.5 (success test with >=5.5~)

Installation

The last stable release is available on PyPI and can be installed with pip:

$ pip install PyMysqlPool

You can installed with easy_install:

$ easy_install PyMysqlPool

You can installed with manually:

$ git clone https://github.com/LuciferJack/python-mysql-pool.git or  download  ***.tar.gz

$ cd PyMysqlPool-***

$ python setup.py install

Documentation

Documentation is available online: http://PyMysqlPool.readthedocs.io/

For support, please refer to the StackOverflow.

Example

The following prototype pool examples below:

step:1

"""
file: new a mysql_config.py file and change to your db config
"""
db_config = {
    'local': {
        'host': "10.95.130.***", 'port': 8899,
        'user': "root", 'passwd': "****",
        'db': "marry", 'charset': "utf8",
        'isolation_level': 'READ COMMITTED',
        'pool': {
            #use = 0 no pool else use pool
            "use":1,
            # size is >=0,  0 is dynamic pool
            "size":0,
            #pool name
            "name":"local",
        }
    },
    'poi': {
        'host': "10.95.130.***", 'port': 8787,
        'user': "lujunxu", 'passwd': "****",
        'db': "poi_relation", 'charset': "utf8",
        'pool': {
            #use = 0 no pool else use pool
            "use":0,
            # size is >=0,  0 is dynamic pool
            "size":0,
            #pool name
            "name":"poi",
        }
    },
}

step:2

"""
Note:create your own table
"""

step:3 (example show below)

from PyMysqlPool.db_util.mysql_util import query,query_single,insertOrUpdate,

"""
pool size special operation
"""
def query_pool_size():
    job_status = 2
    _sql = "select *  from master_job_list j  where j.job_status  in (%s) "
    _args = (job_status,)
    task = query(db_config['local'], _sql,_args)
    logging.info("query_npool method query_npool result is %s ,input _data is %s ", task , _args)
    return

"""
single query
"""
def query_npool():
    job_status = 2
    _sql = "select *  from master_job_list j  where j.job_status  !=%s "
    _args = (job_status,)
    task = query_single(db_config['local'], _sql,_args)
    logging.info("query_npool method query_npool result is %s ,input _data is %s ", task , _args)
    return

"""
insert
"""
def insert(nlp_rank_id,hit_query_word):
    #add more args
    _args = (nlp_rank_id,hit_query_word)
    _sql = """INSERT INTO nlp_rank_poi_online (nlp_rank_id,hit_query_word,rank_type,poi_list,poi_raw_list,article_id,city_id,status,create_time,version,source_from) VALUES (%s,%s,%s, %s, %s,%s, %s,%s, %s,%s,%s)"""
    affect = insertOrUpdate(db_config['local'], _sql, _args)
    logging.info("insert method insert result is %s ,input _data is %s ", affect , _args)
    return

"""
update
"""
def update(query_word,query_id):
    _args = (query_word,query_id)
    _sql = """update nlp_rank  set query_word = %s  WHERE  id = %s"""
    affect = insertOrUpdate(db_config['local'], _sql, _args)
    logging.info("update method update result is %s ,input _data is %s ", affect , _args)
    return

Django use example:

"""
file:settings.py
change to your db config
"""
DATABASES = {
'default': {
    'ENGINE': 'PyMysqlPool.mysql.connector.django',
    'NAME': 'django',
    'USER': 'root',
    'PASSWORD': '*******',
    'HOST': '10.95.130.***',
    'PORT': '8899',
    'OPTIONS': {
        'isolation_level': 'READ COMMITTED',
        'autocommit': True,
        'pool': {
            #use = 0 no pool else use pool
            "use":1,
            # size is >=0,  0 is dynamic pool
            "size":0,
            #pool name
            "name":"local",
        }
     },
   }
 }

Flask use example:

"""
change to your db config
"""
from PyMysqlPool.mysql.connector.flask.mysql import MySQL

app = Flask(__name__,template_folder='flaskPoolShowcase/flask_templates')
#mysql config
app.config.update(
    DEBUG=False,
    MYSQL_DATABASE_HOST='10.95.130.***',
    MYSQL_DATABASE_PORT=8899,
    MYSQL_DATABASE_USER='root',
    MYSQL_DATABASE_PASSWORD='******',
    MYSQL_DATABASE_DB='flask',
    MYSQL_ISOLATION_LEVEL='READ COMMITTED',
    MYSQL_USE_POOL=
    {
        #use = 0 no pool else use pool
        "use":0,
        # size is >=0,  0 is dynamic pool
        "size":10,
        #pool name
        "name":"local",
    },
)
mysql = MySQL()
mysql.init_app(app)

or use the connection type like prototype method.

Resources

python mysql connector: https://dev.mysql.com/downloads/connector/python/

MySQL Reference Manuals: http://dev.mysql.com/doc/

MySQL client/server protocol: http://dev.mysql.com/doc/internals/en/client-server-protocol.html

PyMysqlPool mailing list: https://groups.google.com/forum/#!forum/PyMysqlPool-users

License

PyMysqlPool is released under the MIT License. See LICENSE for more information.

Plan

Dynamic Load Optimization.
Minimum number of connections to maximum performance.

Scope

Now use in BaiDu off-line calculation module.
Like this project, welcome to use and to enhance together.

Frequency Ask

• Django support -- test on one of the following:

  Django 1.11.5 show case: https://github.com/LuciferJack/Django-pool-showcase

• Flask support -- test on one of the following:

  Flask 0.12.2 show case: https://github.com/LuciferJack/Flask-pool-showcase