MySQL&ES连接池

数据库的连接池建议放在类似settings.py的配置模块中，因为基本都是配置项，方便统一管理。

1) 连接池类＃settings.py

import os

from DBUtils.PooledDB import PooledDB

from elasticsearch import Elasticsearch

import pymysql

class Config(object):

    POOL = PooledDB(

        creator = pymysql,

        maxconnections = 20,

        mincached = 3,

        maxcached = 3,

        host = '10.208.116.41',

        port = 3306,

        user = 'hadoop',

        password= 'XXXXX',

        database = 'hadoop_tools'

    )

    #elasticsearch connect

    es_conn = Elasticsearch(

         ['10.208.116.33', '10.208.116.34', '10.208.116.35'],

         sniff_timeout=60,

         sniff_on_start=True

    )

    es_mapping = {

        'properties': {

            'title': {

                'type': 'text',

                'analyzer': 'ik_max_word',

                'search_analyzer': 'ik_max_word'

            }

        }

    }

#POOL test

if __name__ == '__main__':

    conn = Config.POOL.connection()

    #cur = conn.cursor(pymysql.cursors.DictCursor)

    cur = conn.cursor()

    cur.execute("select * from hs2_status")

    ret = cur.fetchall()

    for i in ret:

        print(i)

２）封装ＭySQL的操作

utils/helper.py

import pymysql

from ops.settings import Config

def connect():

    conn = Config.POOL.connection()

    cur = conn.cursor(pymysql.cursors.DictCursor)

    return conn,cur

def connect_close(conn,cur):

    conn.close()

    cur.close()

def fetch_all(sql,args):

    conn,cur = connect()

    cur.execute(sql,args)

    result = cur.fetchall()

    connect_close(conn,cur)

    return result

def fetch_one(sql,args):

    conn,cur = connect()

    cur.execute(sql,args)

    result = cur.fetchone()

    connect_close(conn,cur)

    return result

def insert(sql,args):

    conn, cur = connect()

    row = cur.execute(sql,args)

    conn.commit()

    connect_close(conn,cur)

    return row

def update(sql,args):

    conn, cur = connect()

    row = cur.execute(sql,args)

    conn.commit()

    connect_close(conn,cur)

    return row

def delete(sql,args):

    conn, cur = connect()

    row = cur.execute(sql,args)

    conn.commit()

    connect_close(conn,cur)

    return row


##下面这段调试用，可以去掉

if __name__ == '__main__':

    in_zk = 'YES'

    ip = '10.20８.106.159'

    # row = update("update hs2_status set in_zk=%s  where host_ip = %s",(in_zk,ip))

    # print("row is %s" %(row))

    # 获得表头

    # sql = "SHOW FIELDS FROM hs2_status"

    # tb_h = fetch_one(sql,())  # 执行sql

    # #hs2_header = [l[0] for l in tb_h]

    # # for i in tb_h:

    # print("this is %s" %(tb_h))

    #获取内容

    # hs2_content = fetch_all(sql, ())

    sql = "select * from hs2_status"

    hs2_content = fetch_all(sql,())

    for i in hs2_content:

        print(i)

3)ES连接引用示例：

#!/usr/bin/env python

# encoding: UTF-8

from ops.settings import Config

#create index,change mappings

Config.es_conn.indices.create(index='news', ignore=400)

Config.es_conn.indices.put_mapping(index='news', doc_type='politics', body=Config.es_mapping)

datas = [

    {

        'title': '美国留给伊拉克的是个烂摊子吗',

        'url': 'http://view.news.qq.com/zt2011/usa_iraq/index.htm',

        'date': '2011-12-16'

    },

    {

        'title': '公安部：各地校车将享最高路权',

        'url': 'http://www.chinanews.com/gn/2011/12-16/3536077.shtml',

        'date': '2011-12-16'

    },

    {

        'title': '中韩渔警冲突调查：韩警平均每天扣1艘中国渔船',

        'url': 'https://news.qq.com/a/20111216/001044.htm',

        'date': '2011-12-17'

    },

    {

        'title': '中国驻洛杉矶领事馆遭亚裔男子枪击 嫌犯已自首',

        'url': 'http://news.ifeng.com/world/detail_2011_12/16/11372558_0.shtml',

        'date': '2011-12-18'

    }

]

#insert data

for data in datas:

    Config.es_conn.index(index='news', doc_type='politics', body=data)

#query data

result = Config.es_conn.search(index='news')

print(result)

巴特西

MySQL&ES连接池

最新文章

热门文章