def _PooledDB(db, keywords):
# In DBUtils 0.9.3, `dbapi` argument is renamed as `creator`
# see Bug#122112
if PooledDB.__version__.split('.') < '0.9.3'.split('.'):
return PooledDB.PooledDB(dbapi=db, **keywords)
else:
return PooledDB.PooledDB(creator=db, **keywords)
def get_pooled_db():
from DBUtils import PooledDB
# In DBUtils 0.9.3, `dbapi` argument is renamed as `creator`
# see Bug#122112
if PooledDB.__version__.split('.') < '0.9.3'.split('.'):
return PooledDB.PooledDB(dbapi=self.db_module, **keywords)
else:
return PooledDB.PooledDB(creator=self.db_module, **keywords)
def __init__(self, config_template=config_templates, *args, **kwargs):
self.config = {
'creator': pymysql,
'host': config_template['MYSQL']['HOST'],
'port': config_template['MYSQL']['PORT'],
'user': config_template['MYSQL']['USER'],
'password': config_template['MYSQL']['PASSWD'],
'db': config_template['MYSQL']['DB'],
'charset': config_template['MYSQL']['CHARSET'],
'maxconnections': 70,
'cursorclass': pymysql.cursors.DictCursor
}
self.pool = PooledDB(**self.config)
def getCon(cls, confSection, database):
"""创建连接池，并从池中返回对应的数据库处理工具类
:param confSection: 配置名
:type confSection: string
:param database: 数据库名
:type database: string
"""
key = confSection + ':' + database
clz = ''
if key not in cls.pools:
dbType = ConfigUtil.get(confSection, 'Type', path='/config/test.conf')
mincached = 10
maxcached = 20
maxshared = 15
if dbType == 'mysql':
host = ConfigUtil.get(confSection, 'Host', path='/config/test.conf')
port = ConfigUtil.getInt(confSection, 'Port', path='/config/test.conf')
user = ConfigUtil.get(confSection, 'User', path='/config/test.conf')
pwd = ConfigUtil.get(confSection, 'Passwd', path='/config/test.conf')
charset = 'utf8'
dbParam = {'host': host, 'port': port, 'user': user, 'passwd': pwd, 'db': database, 'charset': charset, }
pool = PooledDB.PooledDB(MySQLdb, mincached, maxcached, maxshared, **dbParam)
clz = MysqlUtil
cls.pools[key] = (pool, clz)
elif dbType == "sqlserver":
host = ConfigUtil.get(confSection, 'Host', path='/config/test.conf')
port = ConfigUtil.getInt(confSection, 'Port', path='/config/test.conf')
user = ConfigUtil.get(confSection, 'User', path='/config/test.conf')
pwd = ConfigUtil.get(confSection, 'Passwd', path='/config/test.conf')
as_dict = True
dbParam = {'host': host, 'port': port, 'user': user, 'password': pwd,
'database': database, 'as_dict': as_dict, }
pool = PooledDB.PooledDB(pymssql, mincached, maxcached, maxshared, **dbParam)
clz = SqlserverUtil
cls.pools[key] = (pool, clz)
else:
raise NotImplementedError
clz = cls.pools[key][1]
databasePool = cls.pools[key][0]
return clz(databasePool.connection())
def getDataSource(self):
mysqlResource = LoadYaml.getLoadYaml(
"D:\\github\pySpider\\dataBaseCon\\resource\\application.yml")
host = mysqlResource['mariadb.host']
port = mysqlResource['mariadb.port']
user = mysqlResource['mariadb.username']
pwd = mysqlResource['mariadb.password']
mincached = mysqlResource['mariadb.mincached']
maxcached = mysqlResource['mariadb.maxcached']
maxconnections = mysqlResource['mariadb.maxconnections']
blocking = mysqlResource['mariadb.blocking']
maxshared = mysqlResource['mariadb.maxshared']
db = mysqlResource['mariadb.db']
poolDb = PooledDB(creator=pymysql,
mincached=mincached,
maxcached=maxcached,
maxshared=maxshared,
maxconnections=maxconnections,
blocking=blocking,
host=host,
port=port,
user=user,
passwd=pwd,
db=db)
return poolDb
def _get_mysql(mysql_config):
import pymysql
from DBUtils import PooledDB
if mysql_config['pool_size'] == 0:
__mysql = pymysql.connect(host=mysql_config['host'],
user=mysql_config['user_name'],
passwd=mysql_config['password'],
port=mysql_config['port'],
db=mysql_config['db_name'],
charset="utf8")
if not __mysql:
from pymysql import MySQLError
raise MySQLError("数据库连接失败")
return DbObject(connection=__mysql)
else:
config = mysql_pool_config
config['mincached'] = mysql_config['pool_size']
config['host'] = mysql_config['host']
config['user'] = mysql_config['user_name']
config['passwd'] = mysql_config['password']
config['port'] = mysql_config['port']
config['db'] = mysql_config['db_name']
pool = PooledDB.PooledDB(pymysql, **config)
if not pool:
from DBUtils.PooledDB import PooledDBError
raise PooledDBError("连接池初始化失败")
return DbObject(pool=pool)
def __init__(self):
if not self.__class__._inited:
self._pool = PooledDB.PooledDB(umysqldb, config.DBPoolCfg['mincached'], config.DBPoolCfg['maxcached'], \
config.DBPoolCfg['maxshared'], config.DBPoolCfg['maxconnections'], config.DBPoolCfg['blocking'], \
host=config.DB_HOST, user=config.DB_USER, passwd=config.DB_PASSWD, \
db=config.DB_SELECT, port=int(config.DB_PORT), charset=config.DB_CHARSET)
self.__class__._inited = True
def _init_connect(self):
'初始化连接'
try:
self._conn = PooledDB.connect(**self._config)
self._cursor = self._conn.cursor()
except Exception as e:
logging.error(e)
def _get_con():
'''
获取数据库连接
'''
host = MYSQL['HOST']
user = MYSQL['USER']
passwd = MYSQL['PASSWD']
db = MYSQL['DB']
port = int(MYSQL['PORT'])
i = 0
while True:
if i > 10:
save_log('ERROR', 'pooldb connection error !')
sys.exit()
time.sleep(round(float(random.randrange(0, 100, 1)) / 100, 2))
try:
pooldb = PooledDB.PooledDB(MySQLdb,
maxusage=MYSQL['SIZE'],
host=host,
user=user,
passwd=passwd,
db=db,
port=port,
charset='utf8')
return pooldb.connection()
except Exception as e:
i = i + 1
continue
def pool(host, database, port=3306, user="******", password=None, mincached=5):
return PooledDB.PooledDB(host=host,
db=database,
port=port,
user=user,
passwd=password,
creator=MySQLdb,
mincached=mincached)
def _init_connect(self):
"""初始化连接"""
try:
self.__conn = PooledDB.connect(**self._config)
self.__cursor = self.__conn.cursor()
except Exception as e:
logging.error(e)
if self.__conn is None:
raise ValueError("Failed to initialize connection.")
def _connect_db(self):
#**self.config传入参数字典
#self.db = mysql.connect(**self.config)
print self.config
self.pool = PooledDB.PooledDB(mysql, **self.config)
self.db = self.pool.connection()
#self.db.autocommit(True)
#使用cursor获取操作游标
self.cur = self.db.cursor()
def __init__(self, _host, _db, _charset, _user, _passwd, _cached):
self.check_conn(_host, _db, _user, _passwd)
self.pool = PooledDB.PooledDB(MySQLdb,
mincached=_cached,
maxcached=_cached + 10,
host=_host,
user=_user,
passwd=_passwd,
db=_db,
charset=_charset)
class MysqlPool:
def __init__(self, config_template=config_templates, *args, **kwargs):
self.config = {
'creator': pymysql,
'host': config_template['MYSQL']['HOST'],
'port': config_template['MYSQL']['PORT'],
'user': config_template['MYSQL']['USER'],
'password': config_template['MYSQL']['PASSWD'],
'db': config_template['MYSQL']['DB'],
'charset': config_template['MYSQL']['CHARSET'],
'maxconnections': 70,
'cursorclass': pymysql.cursors.DictCursor
}
self.pool = PooledDB(**self.config)
def __enter__(self):
try:
self.conn = self.pool.connection()
self.cursor = self.conn.cursor()
except:
raise ValueError('mysql connect error {0}'.format(self.host))
return self
# 释放资源
def __del__(self):
self.cursor.close()
self.conn.close()
def __exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
self.conn.close()
# 查询
def query(self, sql):
try:
with self:
self.cursor.execute(sql)
res = self.cursor.fetchall()
except Exception as e:
logging.error("error", e)
raise e
return res
def change(self, sql):
resnum = 0
try:
with self:
resnum = self.cursor.execute(sql)
self.conn.commit()
except Exception as e:
# 错误回滚
logging.error("error", e)
self.conn.rollback()
return resnum
def __init__(self):
self.pool = PooledDB.PooledDB(MySQLdb,
user=const.MYSQL_USER,
passwd=const.MYSQL_PASSWD,
host=const.MYSQL_HOST,
port=const.MYSQL_PORT,
db=const.MYSQL_DATABASE,
mincached=3,
maxcached=10,
maxshared=3,
maxconnections=100
)
def reconnect(self):
self.close()
try:
from DBUtils import PooledDB
pool_con = PooledDB.PooledDB(creator=MySQLdb, mincached=1, maxcached=10, maxshared=10,\
maxconnections=20, blocking=False, maxusage=100, **self._db_args)
self._db = pool_con.connection()
self._db.cursor().connection.autocommit(True)
except:
self._db = MySQLdb.connect(**self._db_args)
self._db.autocommit(True)
def __init__(self):
# read from config file
self.host = config.get('DATABASE', 'MYSQL_HOST')
self.port = int(config.get('DATABASE', 'MYSQL_PORT'))
self.user = config.get('DATABASE', 'MYSQL_USER')
self.passwd = config.get('DATABASE', 'MYSQL_PASSWD')
self.db = config.get('DATABASE', 'MYSQL_DB')
# mysql database initialize
'''
self._db = pymysql.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.passwd,
db=self.db,
charset="utf8",
autocommit=True)
'''
# init self.pool
self.pool = PooledDB(pymysql, db=self.db, host=self.host, user=self.user, passwd=self.passwd, port=self.port,
charset="utf8", use_unicode=True, autocommit=True)
self.conn = self.pool.connection(shareable=True)
def __init__(self, db_config):
if db_config is None:
raise Exception("load config error")
self.creator = __import__(db_config["creator"])
self.host = db_config['host']
self.port = int(db_config['port'])
self.passwd = db_config['password']
self.username = db_config['username']
self.db = db_config['db']
self.pool = dbUtil.PooledDB(creator=self.creator,
host=self.host,
port=self.port,
user=self.username,
password=self.passwd,
db=self.db)
def msyql_handler():
host = loadconfig('MYSQL', 'HOST')
user = loadconfig('MYSQL', 'USER')
passwd = loadconfig('MYSQL', 'PASSWD') or ''
db = loadconfig('MYSQL', 'DB')
port = int(loadconfig('MYSQL', 'PORT'))
size = int(loadconfig('MYSQL', 'SIZE'))
pooldb = PooledDB.PooledDB(MySQLdb,
maxusage=size,
host=host,
user=user,
passwd=passwd,
db=db,
port=port,
charset='utf8')
return pooldb.connection()
def CreateConnectionPool(self):
try:
pool = PooledDB.PooledDB(pymysql,mincached=self.__mincached, maxconnections=self.__maxconn, maxcached=self.__maxcached,
user=self.__username, passwd=self.__password, host=self.__hostname, port=self.__port, db=self.__db,charset=self.__charset)
return pool
except Exception, e:
LoggerFactory.error("CreateConnectionPool", "Failed to create connection pool with exception: " + str(e))
# self.logger.error("Failed to create connection pool with exception: " + str(e) )
return None
def __init__(self):
# 初始化数据库连接
self.__config = dict({
"host": self.__host,
"port": self.__port,
"user": self.__user,
"password": self.__password,
"db": self.__db,
"charset": self.__charset,
"cursorclass": self.__cursorclass
})
try:
self.__conn = PooledDB.connect(creator=pymysql, **self.__config)
self.__cursor = self.__conn.cursor()
except pymysql.err.OperationalError as e:
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
logging.info('open')
self.close()
try:
from DBUtils import PooledDB
self.pool_con = PooledDB.PersistentDB(creator=MySQLdb,
mincached=1,
maxcached=10,
maxshared=10,
maxconnections=20,
blocking=False,
maxusage=500,
**self._db_args)
except:
self._db = MySQLdb.connect(**self._db_args)
self._db.autocommit(True)
def getCon(cls, confSection, confFile='/config/test.yml'):
"""创建连接池，并从池中返回对应的数据库处理工具类
:param confSection: 配置名
:type confSection: string
:param database: 数据库名
:type database: string
"""
database = ConfigUtil.get(confSection, 'Database', confFile)
key = confSection + ':' + database
clz = ''
if key not in cls.pools:
dbType = ConfigUtil.get(confSection, 'Type', confFile)
mincached = 10
maxcached = 20
maxshared = 15
if dbType == 'mysql':
conf = ConfigUtil.get(confSection, path=confFile)
host = conf.get('Host')
port = conf.get('Port')
user = conf.get('User')
pwd = conf.get('Passwd')
charset = conf.get('charset', 'utf8')
dbParam = {
'host': host,
'port': port,
'user': user,
'passwd': pwd,
'db': database,
'charset': charset,
'cursorclass': pymysql.cursors.DictCursor
}
pool = PooledDB.PooledDB(pymysql, mincached, maxcached,
maxshared, **dbParam)
clz = MysqlUtil
cls.pools[key] = (pool, clz)
else:
raise NotImplementedError
databasePool = cls.pools[key][0]
clz = cls.pools[key][1]
return clz(databasePool.connection())
def __getConn():
"""
Static method, remove the connection from the connection pool
:return MySQLdb.connection
"""
if Mysql.__pool is None:
__pool = PooledDB.PooledDB(creator=pymysql,
mincached=1,
maxcached=20,
host=Config.DBHOST,
port=Config.DBPORT,
user=Config.DBUSER,
passwd=Config.DBPWD,
db=Config.DBNAME,
use_unicode="UTF-8",
charset=Config.DBCHAR,
cursorclass=cursors.DictCursor)
return __pool.connection()
return None
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
self.close()
try:
from DBUtils import PooledDB
pool_con = PooledDB.PooledDB(creator=pymysql,
mincached=1,
maxcached=10,
maxshared=10,
maxconnections=20,
blocking=False,
maxusage=100,
**self._db_args)
self._db = pool_con.connection()
self._db.cursor().connection.autocommit(True)
except:
self._db = pymysql.connect(**self._db_args)
self._db.autocommit(True)
def createConnectionPool(connstring, dbtype):
db_conn = connstring.split("#")
if dbtype == 'mysql':
try:
pool = PooledDB.PooledDB(MySQLdb,
user=db_conn[0],
passwd=db_conn[1],
host=db_conn[2],
port=string.atoi(db_conn[3]),
db=db_conn[4],
charset=db_conn[5],
mincached=mincached,
maxcached=maxcached,
maxshared=maxshared,
maxconnections=maxconn)
return pool
except Exception, e:
raise Exception, 'conn datasource Excepts,%s!!!(%s).' % (
db_conn[2], str(e))
return None
def createPool():
if not DBPool.pool:
from sgLib.setting import Setting
if Setting.getDatabase():
DB_SELECT = Setting.getDatabase() #在gateway设置的数据库 不配置的
else:
DB_SELECT = cfg.DB_SELECT
print '>>>创建连接池 ', DB_SELECT
DBPool.pool = PooledDB.PooledDB(umysqldb,
cfg.DBPoolCfg['mincached'],
cfg.DBPoolCfg['maxcached'],
cfg.DBPoolCfg['maxshared'],
cfg.DBPoolCfg['maxconnections'],
cfg.DBPoolCfg['blocking'],
ping=0,
host=cfg.DB_HOST,
user=cfg.DB_USR,
passwd=cfg.DB_PWD,
db=DB_SELECT,
port=int(cfg.DB_PORT),
charset=cfg.DB_CHARSET) #创建连接池
def create_connection_pool(self, connstring=None, dbtype=None):
if connstring == None:
connstring = self.connstring
if dbtype == None:
dbtype = self.dbtype
db_conn = connstring.split("#")
if dbtype == 'mysql':
try:
pool = PooledDB.PooledDB(MySQLdb,
user=db_conn[0],
passwd=db_conn[1],
host=db_conn[2],
port=string.atoi(db_conn[3]),
db=db_conn[4],
charset=db_conn[5],
mincached=self.mincached,
maxcached=self.maxcached,
maxshared=self.maxshared,
maxconnections=self.maxconn)
return pool
except Exception, e:
raise Exception, 'conn datasource Excepts , %s !!! (%s).' % (
db_conn[2], str(e))
def create_connection_pool(self, dbtype):
try:
if dbtype is None or dbtype not in MysqlConf.DB:
pass
if dbtype is MysqlConf.DB.wind:
pass
# pool = PooledDB.PooledDB(MySQLdb, user=self.user, passwd=self.pwd, host=self.host,
# port=int(self.port), db=self.wind_db, charset=self.charset,
# cursorclass=DictCursor,
# mincached=int(self.wind_min_cached), maxcached=int(self.wind_max_cached),
# maxshared=int(self.wind_max_shared),
# maxconnections=int(self.wind_pool_num), use_unicode=False)
# return pool
else:
pool = PooledDB.PooledDB(MySQLdb, user=self.user, passwd=self.pwd, host=self.host,
port=int(self.port), db=self.fof_db, charset=self.charset,
cursorclass=DictCursor,
mincached=int(self.fof_min_cached), maxcached=int(self.fof_max_cached),
maxshared=int(self.fof_max_shared),
maxconnections=int(self.fof_pool_num), use_unicode=False)
return pool
except Exception as e:
raise Exception('conn datasource Excepts,%s!!!(%s).' % (self, str(e)))
import pymysql
from DBUtils import PooledDB
POOL = PooledDB.PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=6, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=5, # 链接池中最多闲置的链接，0和None不限制
maxshared=3, # 链接池中最多共享的链接数量，0和None表示全部共享。
# PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，
# _maxcached永远为0，所以永远是所有链接都共享。
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=
[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。
# 如：0 = None = never,
# 1 = default = whenever it is requested,
# 2 = when a cursor is created,
# 4 = when a query is executed,
# 7 = always
host="127.0.0.1",
port=3306,
user="******",
password="",
charset="utf8",
db="db3")
