luciferjack/python-mysql-pool

If you use python and mysql, maybe one day you want it. base on pure mysql-connector ,auto manage connection and support 【no pool connect 、fixed 、dynamic pool】. Now used inBaidu poi off-line calculation system . ------------------- Open your favorite Terminal and run the modlue

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

python-mysql-pool's Issues

raise errors.get_exception(packet) django.db.utils.DatabaseError

raise errors.get_exception(packet)
django.db.utils.DatabaseError: Incorrect datetime value: '2017-09-21 09:51:12.769000+00:00' for column 'applied' at row 1
fixed:

Django-pool-showcase

use PyMysqlPool example img show
show case

can't multiply sequence by non-int of type 'tuple'

return "VALUES " + ", ".join([items_sql] * num_values)
TypeError: can't multiply sequence by non-int of type 'tuple'

Lib is printing all logs into syslog file

Hi Team,
if i used that package, it's printing all python packages print, debug, warning and notice into syslog which is increasing syslog file in GB's in few minutes.

doc page not found

http://PyMysqlPool.readthedocs.io/
broken url ?

it doesn't support python3.5

it use MySQLdb to connect mysql, but not support python3.5 and python3.6 and more.

is it not supporting to fetchOne method

Shall we use the fetchOne method and next method in it?

[Question] Unable to connect to MySQL DB from remote machine:mysql.connector.errors.DatabaseError: 1130 (HY000): Host 'machine.xxx.xxx.com' is not allowed to connect to this MySQL server

I am new to this world so I do anything wrong please guide me.

I am using MySQL DB server 8.0 and trying to connect from a remote machine using below Python code:
import mysql.connector mydb = mysql.connector.connect(host='10.xx.xxx.xx', user='dummyuser', passwd='dummypwd') myCursor = mydb.cursor() myCursor.execute("show databases") for i in myCursor: print(i)

I get below error:
mysql.connector.errors.DatabaseError: 1130 (HY000): Host 'xxxxxx-xxx-xxx.xxx.xxx.com' is not allowed to connect to this MySQL server

I checked on the net but nothing seems to work for me can you please guide how can I get this resolved.