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")