摘要：
建议将数据库的连接池放置在类似于settings.py的配置模块中。因为它基本上是一个配置项，所以便于统一管理。1） 连接池类#设置。pyimportosfromDBUtils PooledDBimportPooledDBfromselasticsearchimportElasticearchimportpymysqlclassConfig（对象）：POOL=PooledDB（创建者=
数据库的连接池建议放在类似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)