def test09_EquallyShared(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety shareable = threadsafety > 1 pool = PooledDB(dbapi, 5, 5, 5) self.assertEqual(len(pool._idle_cache), 5) for i in range(15): db = pool.connection(False) db.cursor().execute('select test') db.close() self.assertEqual(len(pool._idle_cache), 5) for i in range(5): con = pool._idle_cache[i] self.assertEqual(con._usage, 3) self.assertEqual(con._con.num_queries, 3) cache = [] for i in range(35): db = pool.connection() db.cursor().execute('select test') cache.append(db) del db self.assertEqual(len(pool._idle_cache), 0) if shareable: self.assertEqual(len(pool._shared_cache), 5) for i in range(5): con = pool._shared_cache[i] self.assertEqual(con.shared, 7) con = con.con self.assertEqual(con._usage, 10) self.assertEqual(con._con.num_queries, 10) del cache self.assertEqual(len(pool._idle_cache), 5) if shareable: self.assertEqual(len(pool._shared_cache), 0)
def test14_SetSession(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety setsession = ('set time zone', 'set datestyle') pool = PooledDB(dbapi, 0, 0, 0, 1, False, None, setsession) self.assertEqual(pool._setsession, setsession) db = pool.connection(False) self.assertEqual(db._setsession_sql, setsession) self.assertEqual(db._con._con.session, ['time zone', 'datestyle']) db.cursor().execute('select test') db.cursor().execute('set test1') self.assertEqual(db._usage, 2) self.assertEqual(db._con._con.num_uses, 4) self.assertEqual(db._con._con.num_queries, 1) self.assertEqual(db._con._con.session, ['time zone', 'datestyle', 'test1']) db.close() db = pool.connection(False) self.assertEqual(db._setsession_sql, setsession) self.assertEqual(db._con._con.session, ['time zone', 'datestyle', 'test1', 'rollback']) db._con._con.close() db.cursor().execute('select test') db.cursor().execute('set test2') self.assertEqual(db._con._con.session, ['time zone', 'datestyle', 'test2'])
def test11_Rollback(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety pool = PooledDB(dbapi, 0, 1) self.assertEqual(len(pool._idle_cache), 0) db = pool.connection(False) self.assertEqual(len(pool._idle_cache), 0) self.assertEqual(db._con._con.open_cursors, 0) cursor = db.cursor() self.assertEqual(db._con._con.open_cursors, 1) cursor.execute('set doit1') db.commit() cursor.execute('set dont1') cursor.close() self.assertEqual(db._con._con.open_cursors, 0) del db self.assertEqual(len(pool._idle_cache), 1) db = pool.connection(False) self.assertEqual(len(pool._idle_cache), 0) self.assertEqual(db._con._con.open_cursors, 0) cursor = db.cursor() self.assertEqual(db._con._con.open_cursors, 1) cursor.execute('set doit2') cursor.close() self.assertEqual(db._con._con.open_cursors, 0) db.commit() session = db._con._con.session db.close() self.assertEqual(session, [ 'doit1', 'commit', 'dont1', 'rollback', 'doit2', 'commit', 'rollback'])
class RDataBase(Singleton): def __init__(self): if hasattr(self, '_init'): return self._init = True config = RConfig() self._db_pool = PooledDB(MySQLdb, user=config.db_user, passwd=config.db_passwd, host=config.db_host, port=config.db_port, db=config.db_db, mincached=config.db_mincached, maxcached=config.db_maxcached, maxshared=config.db_maxshared, maxconnections=config.db_maxconnections, cursorclass=cursors.DictCursor ) def execute(self, sql, param): try: db = self._db_pool.connection() cursor = db.cursor() result = cursor.execute(sql, param) except Exception as e: print "MySQL Error Execute [%s] %r" % (sql, param) db.close() raise RError(1) db.commit() db.close() return result def query(self, sql, param): try: db = self._db_pool.connection() cursor = db.cursor() result = cursor.execute(sql, param) except Exception as e: print "MySQL Error [%s] %r" % (sql, param) db.close() raise RError(1) result = cursor.fetchall() db.close() return result def begin(self): try: db = self._db_pool.connection() except Exception as e: print "MySQL Error when begin an execute." db.close() raise RError(1) return RDataBaseConnection(db) def commit(self, con): return con.commit()
class TRtgHandler: def __init__(self,config,queue): self.pool = PooledDB(creator=MySQLdb,mincached=10,host=config.MYSQL_SERVER,user=config.MYSQL_USER,passwd=config.MYSQL_PASSWORD,db=config.MYSQL_DATABASE) self.queue = queue def response(self,r_id): conn = self.pool.connection() cur = conn.cursor() cur.execute('select d_id,response.user_id,replyDate,response.content,cat_id,category.thumb,conversation.title from response inner join (conversation inner join category using (cat_id)) using(d_id) \ where r_id=%s',(r_id)) res = cur.fetchone() user = database.fetchUserNoCache(cur,res[1]) escaped = util.escape(res[3]) newContent = util.replaceMentions(cur,escaped) shortContent = util.replaceMentions(cur,escaped,True) cur.close() conn.close() payload = {'date':res[2].isoformat(),'content':newContent,'short':shortContent,'user':user,'r_id':r_id,'d_id':res[0]} self.queue.put(event.Message('/conversation/%d' % (res[0]), 'response',payload)) happening_data = {'user':user,'date':res[2].isoformat(),'category_image':res[5],'category_id':res[4],'d_id':res[0],'title': res[6],'r_id':r_id,'content':newContent} self.queue.put(event.Message('/happening','happening',{'type':'response','data':happening_data})) def conversation(self,d_id): conn = self.pool.connection() cur = conn.cursor() cur.execute('select user_id,postDate,content,category.thumb,cat_id,title from conversation inner join category using (cat_id) \ where d_id=%s',(d_id,)) convo = cur.fetchone() user = database.fetchUserNoCache(cur,convo[0]) newContent = util.escape(convo[2]) payload = {'id':d_id,'date':convo[1].isoformat(),'title':convo[5],'user':user,'content':newContent,'short':util.replaceMentions(cur,newContent,True)} cur.close() conn.close() self.queue.put(event.Message('/category/%d' % (convo[4]),'conversation',payload)) happening_data = {'user':user,'date':convo[1].isoformat(),'category_image':convo[3],'d_id':d_id,'title':convo[5],'content':newContent} self.queue.put(event.Message('/happening','happening',{'type':'post','data':happening_data})) def auth(self,auth): self.queue.put(event.NewAuthKey(auth.user_id,auth.key)) def userModified(self,user_id): conn = self.pool.connection() cur = conn.cursor() user = database.fetchUserNoCache(cur,user_id) cur.close() conn.close() self.queue.put(event.Message('/user/%d' % user_id,'user',user))
class RDataBase(Singleton): def __init__(self, config): self._db_pool = PooledDB(MySQLdb, user=config.db_user, passwd=config.db_passwd, host=config.db_host, port=config.db_port, db=config.db_db, mincached=config.db_mincached, maxcached=config.db_maxcached, maxshared=config.db_maxshared, maxconnections=config.db_maxconnections, cursorclass=cursors.DictCursor ) def execute(self, sql, param): try: db = self._db_pool.connection() cursor = db.cursor() cursor.execute("INSERT into input_params VALUE (now(), %s, %s)", (str(sql), str(param))) result = cursor.execute(sql, param) except Exception as e: print "MySQL Error Execute [%s] %r" % (sql, param) db.close() raise RError(1) db.commit() db.close() return result def query(self, sql, param): try: db = self._db_pool.connection() cursor = db.cursor() cursor.execute("INSERT into input_params VALUE (now(), %s, %s)", (str(sql), str(param))) result = cursor.execute(sql, param) except Exception as e: print "MySQL Error [%s] %r" % (sql, param) db.close() raise RError(1) result = cursor.fetchall() db.close() return result def begin(self): try: db = self._db_pool.connection() except Exception as e: print "MySQL Error when begin an execute." db.close() raise RError(1) return RDataBaseConnection(id) def commit(self, con): return con.commit()
class DataAccess: __singleInstance = None pool = None pool_size = 10 debug_level = 0 def __new__(clz): if not DataAccess.__singleInstance: DataAccess.__singleInstance = object.__new__(clz) return DataAccess.__singleInstance def __init__(self): # mysql self.pool = PooledDB(MySQLdb, self.pool_size, db=db_set.db_dbname , user=db_set.db_user, passwd=db_set.db_pwd, host=db_set.db_ip, charset="utf8") def InsertRow(self, insertStr): conn = self.pool.connection() try: cursor = conn.cursor() cursor.execute('SET NAMES utf8') cursor.execute(insertStr) conn.commit() return cursor.lastrowid except: print("InsertRow: Unexpected error:" , sys.exc_info(), sys.exc_traceback.tb_lineno) return 0 finally: if conn: conn.close() def SelectRow(self, selectStr, where = None): conn = self.pool.connection() try: cursor = conn.cursor() cursor.execute('SET NAMES utf8') if where is not None: cursor.execute(selectStr, where) else: cursor.execute(selectStr) res = cursor.fetchall() return res except: print("SelectRow: Unexpected error:" , sys.exc_info(), sys.exc_traceback.tb_lineno) finally: if conn: conn.close() def debug(self, *print_me): if self.debug_level > 0: print print_me
def test05_CloseAll(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety shareable = threadsafety > 1 pool = PooledDB(dbapi, 10) self.assertEqual(len(pool._idle_cache), 10) pool.close() self.assertEqual(len(pool._idle_cache), 0) pool = PooledDB(dbapi, 10) closed = ['no'] def close(what=closed): what[0] = 'yes' pool._idle_cache[7]._con.close = close self.assertEqual(closed, ['no']) del pool self.assertEqual(closed, ['yes']) pool = PooledDB(dbapi, 10, 10, 5) self.assertEqual(len(pool._idle_cache), 10) if shareable: self.assertEqual(len(pool._shared_cache), 0) cache = [] for i in range(5): cache.append(pool.connection()) self.assertEqual(len(pool._idle_cache), 5) if shareable: self.assertEqual(len(pool._shared_cache), 5) else: self.assertEqual(len(pool._idle_cache), 5) pool.close() self.assertEqual(len(pool._idle_cache), 0) if shareable: self.assertEqual(len(pool._shared_cache), 0) pool = PooledDB(dbapi, 10, 10, 5) closed = [] def close_idle(what=closed): what.append('idle') def close_shared(what=closed): what.append('shared') if shareable: cache = [] for i in range(5): cache.append(pool.connection()) pool._shared_cache[3].con.close = close_shared else: pool._idle_cache[7]._con.close = close_shared pool._idle_cache[3]._con.close = close_idle self.assertEqual(closed, []) del pool if shareable: del cache self.assertEqual(closed, ['idle', 'shared'])
def test10_SortShared(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety shareable = threadsafety > 1 pool = PooledDB(dbapi, 0, 0, 5) cache = [] for i in range(35): db = pool.connection() db.cursor().execute('select test1') db.cursor().execute('select test2') db.cursor().callproc('test3') cache.append(db) del db self.assertEqual(len(pool._idle_cache), 0) if shareable: self.assertEqual(len(pool._shared_cache), 5) for i in range(5): con = pool._shared_cache[i] self.assertEqual(con.shared, 7) con = con.con self.assertEqual(con._usage, 21) self.assertEqual(con._con.num_queries, 14) cache[3] = cache[8] = cache[33] = None cache[12] = cache[17] = cache[34] = None self.assertEqual(len(pool._shared_cache), 5) self.assertEqual(pool._shared_cache[0].shared, 7) self.assertEqual(pool._shared_cache[1].shared, 7) self.assertEqual(pool._shared_cache[2].shared, 5) self.assertEqual(pool._shared_cache[3].shared, 4) self.assertEqual(pool._shared_cache[4].shared, 6) for db in cache: if db: db.cursor().callproc('test4') for i in range(6): db = pool.connection() db.cursor().callproc('test4') cache.append(db) del db for i in range(5): con = pool._shared_cache[i] self.assertEqual(con.shared, 7) con = con.con self.assertEqual(con._usage, 28) self.assertEqual(con._con.num_queries, 14) del cache if shareable: self.assertEqual(len(pool._idle_cache), 5) self.assertEqual(len(pool._shared_cache), 0) else: self.assertEqual(len(pool._idle_cache), 35)
class MysqlPool: '''A class of connect pool to Mysql Database''' def __init__(self, args): # mysql connect params args["charset"] = "utf8" args["setsession"] =['SET AUTOCOMMIT = 1'] # mysql connect pool self.pooldb = PooledDB(MySQLdb, **args) def __del__(self): if self.pooldb: self.pooldb.close() def select(self, sql, args=None): try: conn= self.pooldb.connection() cur = conn.cursor() cur.execute(sql, args) results = cur.fetchall() cur.close() conn.close() return results except Exception as e: Common.log('# MyDatabase select exception: %s %s' % (e, sql)) Common.log(args) return None def execute(self, sql, args=None): try: conn= self.pooldb.connection() cur = conn.cursor() cur.execute(sql, args) cur.close() conn.close() except Exception as e: Common.log('# MyDatabase execute exception: %s %s' % (e, sql)) Common.log(args) def executemany(self, sql, args_list=[]): try: conn= self.pooldb.connection() cur = conn.cursor() cur.executemany(sql, args_list) cur.close() conn.close() except Exception as e: Common.log('# MyDatabase executemany exception: %s %s' % (e, sql)) Common.log(args_list)
class PooledConnectionPolicy(DatabaseConnectionPolicyIface): """This connection policy maintains a pool of connections that are doled out as needed for each transaction. NOTE: Appropriate for multi-threaded applications. NOTE: The connections are NOT shared concurrently between threads. """ def __init__(self): """ Consruct an instance. The instance's open() method must be called to make it ready for acquireConnection() calls. """ self._logger = _getLogger(self.__class__) self._logger.debug("Opening") self._pool = PooledDB(**_getCommonSteadyDBArgsDict()) self._logger.info("Created %s", self.__class__.__name__) return def close(self): """ Close the policy instance and its database connection pool. """ self._logger.info("Closing") if self._pool is not None: self._pool.close() self._pool = None else: self._logger.warning("close() called, but connection policy was alredy closed") return def acquireConnection(self): """ Get a connection from the pool. Parameters: ---------------------------------------------------------------- retval: A ConnectionWrapper instance. NOTE: Caller is responsible for calling the ConnectionWrapper instance's release() method or use it in a context manager expression (with ... as:) to release resources. """ self._logger.debug("Acquiring connection") dbConn = self._pool.connection(shareable=False) connWrap = ConnectionWrapper( dbConn=dbConn, cursor=dbConn.cursor(), releaser=self._releaseConnection, logger=self._logger ) return connWrap def _releaseConnection(self, dbConn, cursor): """ Release database connection and cursor; passed as a callback to ConnectionWrapper """ self._logger.debug("Releasing connection") # Close the cursor cursor.close() # ... then return db connection back to the pool dbConn.close() return
def generate_accuser_url(): pool = PooledDB(MySQLdb, 1, host="localhost", user="******", passwd="123456", db="abnormal") conn = pool.connection() cur = conn.cursor() # objFoleder = 'Report' objFile = open("ParseResult") index = 1 # for objFile in os.listdir(objFoleder): count = 0 while True: line = objFile.readline() if not line: break # print replace_struct_time(line) parDict = eval(replace_struct_time(line)) reportId = parDict["reportId"] # soup = BeautifulSoup(os.path.join(objFoleder, objFile)) # countText = soup.find(class_='W_f12 W_textb').text # accuserCount = int(patternNumber.search(countText).group()) accuserCount = min(parDict["accuserCount"], 20) if accuserCount > 1: for i in xrange(accuserCount - 1): url = "http://service.account.weibo.com/aj/reportuser?rid=%s&page=%d&_t=0" % (reportId, i) sql = 'insert into userlinks values (%d, "%s", 0)' % (index, url) cur.execute(sql) index += 1 print count count += 1 conn.commit() cur.close() conn.close()
def generate_word_url(): pool = PooledDB(MySQLdb, 1, host="localhost", user="******", passwd="123456", db="abnormal") conn = pool.connection() cur = conn.cursor() objFoleder = "Report" index = 1 count = 0 for objFile in os.listdir(objFoleder): reportId = objFile.split("_")[1] soup = BeautifulSoup(open(os.path.join(objFoleder, objFile))) pageTags = soup.find_all(class_="page") pageCountA, pageCountB = 1, 1 if len(pageTags) > 0: pageCountA = len(pageTags[0].find_all("a")) - 1 if len(pageTags) > 1: pageCountB = len(pageTags[1].find_all("a")) - 1 for i in xrange(2, pageCountA + 1): url = "http://service.account.weibo.com/aj/showblog?type=0&rid=%s&page=%d&_t=0" % (reportId, i) sql = 'insert into wordlinks values (%d, "%s", 0)' % (index, url) cur.execute(sql) index += 1 for i in xrange(2, pageCountB + 1): url = "http://service.account.weibo.com/aj/showblog?type=1&rid=%s&page=%d&_t=0" % (reportId, i) sql = 'insert into wordlinks values (%d, "%s", 0)' % (index, url) cur.execute(sql) index += 1 print count count += 1 conn.commit() cur.close() conn.close()
class DBConnectionPool(object): __pool = None def __enter__(self): self.conn = self.getConn() self.cursor = self.conn.cursor() return self def getConn(self): if self.__pool is None: self.__pool = PooledDB(creator=pymysql, mincached=Config.DB_MIN_CACHED, maxcached=Config.DB_MAX_CACHED, maxshared=Config.DB_MAX_SHARED, maxconnections=Config.DB_MAX_CONNECYIONS, blocking=Config.DB_BLOCKING, maxusage=Config.DB_MAX_USAGE, setsession=Config.DB_SET_SESSION, host=Config.DB_TEST_HOST, port=Config.DB_TEST_PORT, user=Config.DB_TEST_USER, passwd=Config.DB_TEST_PASSWORD, db=Config.DB_TEST_DBNAME, use_unicode=False, charset=Config.DB_CHARSET) return self.__pool.connection() """ @summary: 释放连接池资源 """ def __exit__(self, type, value, trace): self.cursor.close() self.conn.close()
class DbManager(object): def __init__(self): connKwargs = {'host':'127.0.0.1', 'user':'******', 'port':3306, 'passwd':'root', 'db':'yii', 'charset':"utf8"} args = (10, 10, 30, 100, True, 0, None) self._pool = PooledDB(MySQLdb, *args, **connKwargs) def getConn(self): return self._pool.connection() def executeAndGetId(self, sql, param=None): """ 执行插入语句并获取自增id """ conn = self.getConn() cursor = conn.cursor() if param == None: cursor.execute(sql) else: cursor.execute(sql, param) id = cursor.lastrowid cursor.close() conn.close() return id def queryAll(self, sql): """ 获取所有信息 """ conn = self.getConn() cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) rowcount = cursor.execute(sql) if rowcount > 0: res = cursor.fetchall() else: res = None cursor.close() conn.close() return res def execute(self,sql, param=None): """ 执行sql语句 """ conn = self.getConn() cursor = conn.cursor() if param == None: rowcount = cursor.execute(sql) else: rowcount = cursor.execute(sql, param) cursor.close() conn.close() return rowcount def queryOne(self,sql): """ 获取一条信息 """ conn = self.getConn() cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) rowcount = cursor.execute(sql) if rowcount > 0: res = cursor.fetchone() else: res = None cursor.close() conn.close() return res
def test13_MaxUsage(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety for maxusage in (0, 3, 7): pool = PooledDB(dbapi, 0, 0, 0, 1, False, maxusage) self.assertEqual(pool._maxusage, maxusage) self.assertEqual(len(pool._idle_cache), 0) db = pool.connection(False) self.assertEqual(db._con._maxusage, maxusage) self.assertEqual(len(pool._idle_cache), 0) self.assertEqual(db._con._con.open_cursors, 0) self.assertEqual(db._usage, 0) self.assertEqual(db._con._con.num_uses, 0) self.assertEqual(db._con._con.num_queries, 0) for i in range(20): cursor=db.cursor() self.assertEqual(db._con._con.open_cursors, 1) cursor.execute('select test%i' % i) r = cursor.fetchone() self.assertEqual(r, 'test%i' % i) cursor.close() self.assertEqual(db._con._con.open_cursors, 0) if maxusage: j = i % maxusage + 1 else: j = i + 1 self.assertEqual(db._usage, j) self.assertEqual(db._con._con.num_uses, j) self.assertEqual(db._con._con.num_queries, j) db.cursor().callproc('test') self.assertEqual(db._con._con.open_cursors, 0) self.assertEqual(db._usage, j + 1) self.assertEqual(db._con._con.num_uses, j + 1) self.assertEqual(db._con._con.num_queries, j)
class MysqlPool(object): __pool = None def __init__(self, host = "", user = "", passwd = "", db = "", port = 3306, charset = "utf8", ): self.host = host self.user = user self.passwd = passwd self.db = db self.port = port self.conn = None self.cursor = None self.charset = charset def connect(self): try: if self.__pool is None: self.__pool = PooledDB(creator = MySQLdb, host = self.host, user = self.user, passwd = self.passwd, db = self.db, port = self.port, charset = self.charset, mincached = 1, maxcached = 20, use_unicode = True, cursorclass = DictCursor ) self.conn = self.__pool.connection() self.cursor = self.conn.cursor() except Exception as info: print info sys.exit(-1) def execute(self, sql, print_sql = True, commit = False): self.connect() if print_sql: print sql self.info = self.cursor.execute(sql) if commit: self.commit() self.conn.close() def commit(self): self.conn.commit() def next(self): return self.cursor.fetchone() def next_all(self): return self.cursor.fetchall()
def test15_OneThreadTwoConnections(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety shareable = threadsafety > 1 pool = PooledDB(dbapi, 2) db1 = pool.connection() for i in range(5): db1.cursor().execute('select test') db2 = pool.connection() self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) for i in range(7): db2.cursor().execute('select test') self.assertEqual(db1._con._con.num_queries, 5) self.assertEqual(db2._con._con.num_queries, 7) del db1 db1 = pool.connection() self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) for i in range(3): db1.cursor().execute('select test') self.assertEqual(db1._con._con.num_queries, 8) db2.cursor().execute('select test') self.assertEqual(db2._con._con.num_queries, 8) pool = PooledDB(dbapi, 0, 0, 2) db1 = pool.connection() for i in range(5): db1.cursor().execute('select test') db2 = pool.connection() self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) for i in range(7): db2.cursor().execute('select test') self.assertEqual(db1._con._con.num_queries, 5) self.assertEqual(db2._con._con.num_queries, 7) del db1 db1 = pool.connection() self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) for i in range(3): db1.cursor().execute('select test') self.assertEqual(db1._con._con.num_queries, 8) db2.cursor().execute('select test') self.assertEqual(db2._con._con.num_queries, 8) pool = PooledDB(dbapi, 0, 0, 1) db1 = pool.connection() db2 = pool.connection() self.assertNotEqual(db1, db2) if shareable: self.assertEqual(db1._con, db2._con) else: self.assertNotEqual(db1._con, db2._con) del db1 db1 = pool.connection(False) self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con)
class DbManager: def __init__(self): connKwargs = {'host':'127.0.0.1','user':'******','passwd':'3508257','db':'nozomi','charset':"utf8"} self._pool = PooledDB(MySQLdb, mincached=1, maxcached=10, maxshared=10, maxusage=10000, **connKwargs) def getConn(self): return self._pool.connection()
class DbManager(object): def __init__(self): conn_kwargs = {'host':configs['DB_HOST'], 'user':configs['DB_USER'], 'passwd':configs['DB_PASS'], 'db':configs['db']} self._pool = PooledDB(MySQLdb, mincached=0, maxcached=10, maxshared=10, maxusage=10000, **conn_kwargs) def getConn(self): return self._pool.connection()
def _connect(host, user, passwd, charset, db): """静态方法,从连接池中取出连接 """ if MySQLDB_.pool is None: pool = PooledDB(MySQLdb, host=host, user=user, passwd=passwd, charset=charset, port=3306, db=db, mincached=1, maxcached=20, maxshared=2, maxconnections=2) return pool.connection()
def __getConn(): """ @summary: 静态方法,从连接池中取出链接 @return MySQLdb.connection """ if Mysql.__pool is None: __pool = PooledDB(creator=MySQLdb, mincache=1, maxcached=20, host=Config.DBHOST, port=Config.DBPORT, user=Config.DBPWD, db=Config.DBNAME, use_unicode=False, charset=Config.DBCHAR, cursorclass=DictCursor) return __pool.connection()
class DBManager(Singleton): def __init__(self): connKwargs = {'host':configs['localhost'], 'user':configs['root'], 'passwd':configs['root'], 'db':configs['Robot'], 'charset':"utf8"} self._pool = PooledDB(MySQLdb, mincached=0, maxcached=10, maxshared=10, maxusage=10000, **connKwargs) def get_connection(self): return self._pool.connection()
def __getConn(): if Mysqldb.__pool is None: __pool = PooledDB(MySQLdb, mincached=1, maxcached=20, maxconnections=20 , host=MysqlConfig.host, port=MysqlConfig.port , user=MysqlConfig.user, passwd=MysqlConfig.password , db=MysqlConfig.dbname#, use_unicode=False, charset=MysqlConfig.charset , cursorclass=DictCursor) return __pool.connection()
def getConnect(self): global SQLPOOL if SQLPOOL is None: SQLPOOL = PooledDB(creator=MySQLdb ,mincached=self.__cachemin , maxcached=0 ,maxshared=0,maxconnections=0,blocking=True,maxusage=0, host=self.__host , port=self.__port , user=self.__user , passwd=self.__passwd, db=self.__db,use_unicode=False,charset=self.__charset ,cursorclass=DictCursor ) return SQLPOOL.connection()
def test16_ThreeThreadsTwoConnections(self): for threadsafety in (1, 2): dbapi.threadsafety = threadsafety pool = PooledDB(dbapi, 2, 2, 0, 2, True) from Queue import Queue, Empty queue = Queue(3) def connection(): try: queue.put(pool.connection(), 1, 1) except Exception: queue.put(pool.connection(), 1) from threading import Thread for i in range(3): Thread(target=connection).start() try: db1 = queue.get(1, 1) db2 = queue.get(1, 1) except TypeError: db1 = queue.get(1) db2 = queue.get(1) self.assertNotEqual(db1, db2) db1_con = db1._con db2_con = db2._con self.assertNotEqual(db1_con, db2_con) try: self.assertRaises(Empty, queue.get, 1, 0.1) except TypeError: self.assertRaises(Empty, queue.get, 0) del db1 try: db1 = queue.get(1, 1) except TypeError: db1 = queue.get(1) self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) self.assertEqual(db1._con, db1_con) pool = PooledDB(dbapi, 2, 2, 1, 2, True) db1 = pool.connection(False) db2 = pool.connection(False) self.assertNotEqual(db1, db2) db1_con = db1._con db2_con = db2._con self.assertNotEqual(db1_con, db2_con) Thread(target=connection).start() try: self.assertRaises(Empty, queue.get, 1, 0.1) except TypeError: self.assertRaises(Empty, queue.get, 0) del db1 try: db1 = queue.get(1, 1) except TypeError: db1 = queue.get(1) self.assertNotEqual(db1, db2) self.assertNotEqual(db1._con, db2._con) self.assertEqual(db1._con, db1_con)
def __getConn(): """ @summary: 静态方法,从连接池中取出连接 @return MySQLdb.connection """ if Mysql.__pool is None: __pool = PooledDB(creator=MySQLdb, mincached=1 , maxcached=20 , host="172.16.130.87" , port=3306 , user="******" , passwd="hta@123" , db="finance_spiderdata",use_unicode=True,charset="utf8",cursorclass=DictCursor) return __pool.connection()
class dbOperator: def __init__(self):#填入你的数据库 self.conn = PooledDB(MySQLdb, maxusage=1, db=g_dbName, host=g_dbHost, user=g_dbUser, passwd=g_dbPasswd, charset='utf8') def insert(self,value): pool = self.conn.connection() cursor = pool.cursor() #value = [None,pid,wbid,content,date] cursor.execute("insert into data values(%s,%s,%s,%s,%s)",value); cursor.close() pool.commit() pool.close() def insertRetweet(self,value): pool = self.conn.connection() cursor = pool.cursor() #value = [None,wbid,username,content,date] cursor.execute("insert into retweet values(%s,%s,%s,%s,%s)",value); cursor.close() pool.commit() pool.close() def updateName(self,upid,value): pool = self.conn.connection() cursor = pool.cursor() #value = [None,name,wbid,content,date] cursor.execute("UPDATE weibo_data SET name = \'%s\' WHERE wbid = \'%s\'" % (value,upid)) cursor.close() pool.commit() pool.close() def selectData(self,sqlstr): pool = self.conn.connection() cursor = pool.cursor() count = cursor.execute(sqlstr) results = cursor.fetchall() cursor.close() pool.close() return results def closeDb(self): self.conn.close()
def get_db_conn(): db_cfg = get_db_cfg() table = db_cfg.pop('table') pool = PooledDB(MySQLdb, mincached=2, maxcached=2, maxshared=2, maxconnections=2, **db_cfg) conn = pool.connection() return conn, table
def __getConn(): """ @summary: 静态方法,从连接池中取出连接 @return MySQLdb.connection """ if Mysql.__pool is None: dbConfig = json.load(open('config.json', 'r')).get('db') #载入配置文件 __pool = PooledDB(creator=MySQLdb, mincached=1 , maxcached=20 , host=dbConfig['host'] , port=dbConfig['port'] , user=dbConfig['user'], passwd=dbConfig['passwd'] , db=dbConfig['db'],charset=dbConfig['charset'],cursorclass=DictCursor) return __pool.connection()
def __getConn(): """ @summary: 静态方法,从连接池中取出连接 @return MySQLdb.connection """ if MysqlClient.__pool is None: __pool = PooledDB(creator=MySQLdb, mincached=1, maxcached=20, host=Config.DBHOST, port=Config.DBPORT, user=Config.DBUSER, passwd=Config.DBPWD, db=Config.DBNAME, use_unicode=True, charset=Config.DBCHAR, cursorclass=DictCursor) if MysqlClient.__mutex is None: MysqlClient.__mutex = threading.Lock() return __pool.connection()
def getConnect(self): global SQLPOOL if SQLPOOL is None: SQLPOOL = PooledDB(creator=MySQLdb, mincached=self.__cachemin, maxcached=0, maxshared=0, maxconnections=0, blocking=True, maxusage=0, host=self.__host, port=self.__port, user=self.__user, passwd=self.__passwd, db=self.__db, use_unicode=False, charset=self.__charset, cursorclass=DictCursor) print type(SQLPOOL) return SQLPOOL.connection()
class MysqlPool(Base): __pool = None def __init__(self, conf_name=None): self.conf = Config().get_content(conf_name) super(MysqlPool, self).__init__(**self.conf) if self.__pool is None: self.__pool = PooledDB(creator=pymysql, mincached=10, maxcached=20, maxconnections=20, maxusage=0, ping=4, host=self.db_host, port=self.db_port, user=self.user, passwd=self.password, db=self.db, use_unicode=True, charset="utf8", cursorclass=DictCursor) def getconn(self): return self.__pool.connection()
class DbManager(Singleton): def __init__(self): settings = get_project_settings() ibm_db_dbi.threadsafety = 1 connKwargs = { 'dsn': 'DATABASE=' + settings['DB2_DBNAME'] + ';HOSTNAME=' + settings['DB2_HOST'] + ';UID=' + settings['DB2_USER'] + ';PWD=' + settings['DB2_PASSWORD'] + ';PORT=' + str(settings['DB2_PORT']), 'conn_options': { 'SQL_ATTR_AUTOCOMMIT': ibm_db_dbi.SQL_AUTOCOMMIT_ON } } self._pool = PooledDB(ibm_db_dbi, mincached=0, maxcached=10, maxshared=10, maxusage=10000, **connKwargs) def getConn(self): return self._pool.connection()
class SqlConnectionPool(object): __pool = None def __enter__(self): '''单个用户连接进入连接池后,即可以执行sql语句进行查询''' try: self.conn = self.__getConn() self.cursor = self.conn.cursor() except DatabaseError: logger.warning('sql connect error') except Exception as e: logger.warning('sql undefined error' + e) return self def __getConn(self): '''连接池的方式连接mysql资料库''' if self.__pool is None: self.__pool = PooledDB(creator=pymysql, mincached=Config.DB_MIN_CACHED, maxcached=Config.DB_MAX_CACHED, maxshared=Config.DB_MAX_SHARED, maxconnections=Config.DB_MAX_CONNECYIONS, blocking=Config.DB_BLOCKING, maxusage=Config.DB_MAX_USAGE, setsession=Config.DB_SET_SESSION, host=Config.DB_TEST_HOST, port=Config.DB_TEST_PORT, user=Config.DB_TEST_USER, passwd=Config.DB_TEST_PASSWORD, db=Config.DB_TEST_DBNAME, use_unicode=False, charset=Config.DB_CHARSET) return self.__pool.connection() def __exit__(self, type, value, trace): '''连接池的释放''' self.cursor.close() self.conn.close()
class SingletonDBPool(object): _instance_lock = threading.Lock() def __init__(self): self.POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=100, # 连接池允许的最大连接数,0和None表示不限制连接数 mincached=10, # 初始化时,链接池中至少创建的空闲的链接,0表示不创建 maxcached=20, # 链接池中最多闲置的链接,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='localhost', port=3306, user='******', password='******', database='s6', charset='utf8', cursorclass=pymysql.cursors.DictCursor, ) def __new__(cls, *args, **kwargs): if not hasattr(SingletonDBPool, "_instance"): with SingletonDBPool._instance_lock: if not hasattr(SingletonDBPool, "_instance"): SingletonDBPool._instance = object.__new__(cls, *args, **kwargs) return SingletonDBPool._instance def connect(self): return self.POOL.connection()
def __init__(self, type=None): """ # sqlite3 # 连接数据库文件名,sqlite不支持加密,不使用用户名和密码 import sqlite3 config = {"datanase": "path/to/your/dbname.db"} pool = PooledDB(sqlite3, maxcached=50, maxconnections=1000, maxusage=1000, **config) # mysql import pymysql pool = PooledDB(pymysql,5,host='localhost', user='******',passwd='pwd',db='myDB',port=3306) #5为连接池里的最少连接数 # postgressql import psycopg2 POOL = PooledDB(creator=psycopg2, host="127.0.0.1", port="5342", user, password, database) # sqlserver import pymssql pool = PooledDB(creator=pymssql, host=host, port=port, user=user, password=password, database=database, charset="utf8") :param type: """ POOL = PooledDB( creator=psycopg2, # 使用链接数据库的模块 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, host=settings.POSTGRESQL_HOST, port=settings.POSTGRESQL_PORT, user=settings.POSTGRESQL_USER, password=settings.POSTGRESQL_PASSWORD, database=settings.POSTGRESQL_DATABASE, ) self.conn = POOL.connection() self.cursor = self.conn.cursor()
class PTConnectionPool(object): __pool = None def __enter__(self): self.conn = self.__getConn() self.cursor = self.conn.cursor() print('PT数据库创建con和cursor') return self def __getConn(self): if self.__pool is None: self.__pool = PooledDB(creator=MySQLdb, mincached=Config.DB_MIN_CACHED, maxcached=Config.DB_MAX_CACHED, maxshared=Config.DB_MAX_SHARED, maxconnections=Config.DB_MAX_CONNECYIONS, blocking=Config.DB_BLOCKING, maxusage=Config.DB_MAX_USAGE, setsession=Config.DB_SET_SESSION, host=Config.DB_TEST_HOST, port=Config.DB_TEST_PORT, user=Config.DB_TEST_USER, passwd=Config.DB_TEST_PASSWORD, db=Config.DB_TEST_DBNAME, use_unicode=False, charset=Config.DB_CHARSET) return self.__pool.connection() """ @summary: 释放连接池资源 """ def __exit__(self, type, value, trace): self.cursor.close() self.conn.close() print("PT连接池释放con和cursor")
def __getConn(self): """ @summary: 静态方法,从连接池中取出连接 @return pymysql.connection """ if self.__pool is None: __pool = PooledDB(creator=pymysql, mincached=Config.DB_MIN_CACHED, maxcached=Config.DB_MAX_CACHED, maxshared=Config.DB_MAX_SHARED, maxconnections=Config.DB_MAX_CONNECYIONS, blocking=Config.DB_BLOCKING, maxusage=Config.DB_MAX_USAGE, setsession=Config.DB_SET_SESSION, host=Config.DBHOST, port=Config.DBPORT, user=Config.DBUSER, passwd=Config.DBPASSWORD, db=Config.DBDBNAME, use_unicode=True, charset=Config.DB_CHARSET, cursorclass=eval(Config.CURSOR_TYPE)) return __pool.connection() #返回db连接对象
class dbPool(object): _instance_lock = threading.Lock() def __new__(cls, *args, **kwargs): if not hasattr(dbPool, "_instance"): with dbPool._instance_lock: if not hasattr(dbPool, "_instance"): dbPool._instance = object.__new__(cls) return dbPool._instance def __init__(self, kwargs): self.__dict__.update(kwargs) self.Pool = PooledDB(creator=pymysql, mincached=3, maxcached=5, charset="utf8", host=self.host, port=int(self.port), user=self.user, passwd=self.password) def getConn(self): return self.Pool.connection()
def __getConn(self): """ :summary: 静态方法,从连接池中取出连接 :return: MySQLdb.connection(PooledDB().connection) """ if not MysqlConnPool._pool: #判断是否已经存在连接池,不存在就使用PooledDB类初始化创建 _pool=PooledDB(creator=pymysql, mincached=self._mincached, maxcached=self._maxcached, host=self._db_host, port=self._db_port, user=self._user, passwd=self._password, db=self._db, use_unicode=False, charset="utf8", cursorclass=DictCursor, setsession=None, *self._args, **self._kwargs, ) #创建一个连接池的数据库对象,可以设置连接池的参数 #setsession=['SET AUTOCOMMIT = 1'] 表示更新自动提交事务,而不用每次都去commit一下 return _pool.connection()#返回一个数据库对象的连接池
def _init_mysql(self): n = 0 while True: try: pool = PooledDB(pymysql, Config.mysql_max_cached, host=Config.mysql_host, user=Config.mysql_user, passwd=Config.mysql_password, db=Config.mysql_dbname, port=Config.mysql_port, charset=Config.mysql_charset) self._conn = pool.connection() break except Exception, e: print Exception, ":", e if n >= Config.mysql_retry_times: print('Mysql Connect Error,exit!') sys.exit() else: n = n + 1 print('Mysql Connect Error,sleep!') sleep(100)
def __getConn(cls): __pool = cls.__pool if cls.__pool is None: success = False count = 0 while not success: try: __pool = PooledDB(creator=MySQLdb, mincached=1, maxcached=100, host=cls.DB_HOST, port=cls.DB_PORT, user=cls.DB_USER, passwd=cls.DB_PWD, db=cls.DB_NAME, use_unicode=True, charset=cls.DB_CHARSET, cursorclass=DictCursor) if __pool is not None: success = True except MySQLdb.OperationalError as e: if e.args[0] in (2006, 2013, 2003, 2006): print("DB-CONNECTION ERROR: ", str(e.args[0]) + "-" + str(e.args[1])) else: print("UNKNOWN DB ERROR: ", str(e.args[0]) + "-" + str(e.args[1])) success = False time.sleep(2) if count > 100000: raise count += 1 return __pool.connection()
class DbManager(object): def __init__(self): #connKwargs = {'user': db_config['username'], 'password': db_config['password'], # 'dsn': db_config['dsn'], 'encoding': db_config['encoding']} #self._pool = PooledDB(cx_Oracle, mincached=0, maxcached=10, maxshared=10, maxusage=10000, **connKwargs) self._pool = PooledDB(cx_Oracle, user="******", password="******", dsn="192.168.1.1:21001/itmtest", mincached=2, maxcached=2, maxshared=2, maxconnections=2) def getConn(self): return self._pool.connection() @classmethod def close(cls, conn, cursor): if cursor is not None: cursor.close() if conn is not None: conn.close()
def __getConn(self): """ @summary: 静态方法,从连接池中取出连接 @return MySQLdb.connection """ if MyPymysqlPool.__pool is None: # creator 传入 phoenix 的产生连接池的函数 __pool = PooledDB( creator=pymysql, mincached=1, maxcached=1000, host=self.mysql_host, port=self.mysql_port, user=self.mysql_user, passwd=self.mysql_password, database=self.mysql_database, use_unicode=True, charset="utf8", # 指定字符编码 autocommit=True, cursorclass=pymysql.cursors.DictCursor, ) self.__pool = __pool return __pool.connection()
def __init__(self): mysql_pool = PooledDB( # 使用的mysql驱动 creator=pymysql, host='127.0.0.1', port='root', user='******', password='******', database='py_test', charset='utf8mb4', # 连接池允许的最大连接数 maxconnections=10, # 初始化时,连接池中至少创建的空闲的链接 mincached=2, # 连接池中最多共享的连接数量 maxshared=3, # 一个连接最多被重复使用的次数,None表示无限制 maxusage=None, ) self.db_conn = mysql_pool.connection() # 设置返回的数据类型为dict,而不是tuple self.cursor = self.db_conn.cursor(cursor=pymysql.cursors.DictCursor)
class DbManager(object): def __init__(self, host, port, db_name, user, pwd): # 启动时连接池中创建的的连接数 #'mincached': 5, # 连接池中最大允许创建的连接数 #'maxcached': 20, conn_args = { 'host': host, 'port': port, 'db': db_name, 'user': user, 'passwd': pwd, 'charset': 'utf8', 'cursorclass': MySQLdb.cursors.DictCursor } self._pool = PooledDB(MySQLdb, mincached=5, maxcached=20, **conn_args) def getConn(self): conn = self._pool.connection() cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) cursor.execute("set names utf8mb4;") cursor.close() return conn
class MysqlPool(object): #设置数据库连接池和初始化 """ MYSQL数据库对象,负责产生数据库连接 , 此类中的连接采用连接池实现 获取连接对象:conn = Mysql.get_connection() 释放连接对象;conn.close()或del conn """ def __init__(self,mincached=5, maxcached=14, maxconnections=244, blocking=True, maxshared=0): """ 生成MySQL数据库连接池 :param mincached: 最少的空闲连接数,如果空闲连接数小于这个数,pool会创建一个新的连接 :param maxcached: 最大的空闲连接数,如果空闲连接数大于这个数,pool会关闭空闲连接 :param maxconnections: 最大的连接数 :param blocking: 当连接数达到最大的连接数时,在请求连接的时候,如果这个值是True,请求连接的程序会一直等待, 直到当前连接数小于最大连接数,如果这个值是False,会报错, :param maxshared: 当连接数达到这个数,新请求的连接会分享已经分配出去的连接 """ db_config = { "host": '47.92.25.70', "port": 3306, "user": '******', "passwd": 'Wfn031641', "db": 'cxd_data', "charset": 'utf8' } self.pool = PooledDB(pymysql, mincached=mincached, maxcached=maxcached, maxconnections=maxconnections, blocking=blocking, maxshared=maxshared, **db_config) def get_connection(self): return self.pool.connection() def close(self): self.pool.close() def __del__(self): self.close()
class PTConnectionPool(object): __pool = None logger = logging.getLogger() def __enter__(self): self.conn = self.getConn() self.cursor = self.conn.cursor() return self def getConn(self): if self.__pool is None: self.__pool = PooledDB( creator=pymysql, mincached=booker_config.getint("datasource", "DB_MIN_CACHED"), maxcached=booker_config.getint("datasource", "DB_MAX_CACHED"), maxshared=booker_config.getint("datasource", "DB_MAX_SHARED"), maxconnections=booker_config.getint("datasource", "DB_MAX_CONNECYIONS"), blocking=booker_config.getboolean("datasource", "DB_BLOCKING"), host=booker_config.get("datasource", "DB_HOST"), port=booker_config.getint("datasource", "DB_PORT"), user=booker_config.get("datasource", "DB_USER"), passwd=booker_config.get("datasource", "DB_PASSWORD"), db=booker_config.get("datasource", "DB_DBNAME"), use_unicode=False, charset=booker_config.get("datasource", "DB_CHARSET"), setsession=['SET AUTOCOMMIT = 1']) return self.__pool.connection() """ @summary: 释放连接池资源 """ def __exit__(self, type, value, trace): self.cursor.close() self.conn.close()
def get_conn(repeat_time=3): """ 获取数据库连接 :return {tuple}: conn, cursor """ conn = None cursor = None global dbpool if dbpool is None: try: global CONFIG dbpool = PooledDB(MySQLdb, **CONFIG['db']) except Exception as e: logging.error('[red]mysql connection error:%s[/red]', e, exc_info=True, extra={'color':True}) raise # 允许出错时重复提交多次,只要设置了 repeat_time 的次数 while repeat_time > 0: try: conn = dbpool.connection() # 尝试连接数据库 conn.ping(True) # cursor = conn.cursor() # py3 下的 pymysql 会导致默认值失效,这里强制使用 dict 返回 cursor = conn.cursor(cursor=MySQLdb.cursors.DictCursor) return conn, cursor # 数据库连接,默认8小时没有使用会自动断开,这里捕获这异常 except Exception as e: repeat_time -= 1 logging.error('[red]mysql connection error:%s[/red]', e, exc_info=True, extra={'color':True}) try: if cursor: cursor.close() except:pass try: if conn: conn.close() except:pass return conn, cursor
class PTConnectionPool(object): __pool = None def __enter__(self): self.conn = self.getConn() self.cursor = self.conn.cursor() return self def getConn(self): if self.__pool is None: self.__pool = PooledDB( creator=pymysql, cursorclass=pymysql.cursors.DictCursor, mincached=int(mysqldb_conn.get('DB_MIN_CACHED')), maxcached=int(mysqldb_conn.get('DB_MAX_CACHED')), maxshared=int(mysqldb_conn.get('DB_MAX_SHARED')), maxconnections=int(mysqldb_conn.get('DB_MAX_CONNECYIONS')), blocking=mysqldb_conn.get('DB_BLOCKING'), setsession=mysqldb_conn.get('DB_SET_SESSION'), maxusage=int(mysqldb_conn.get('DB_MAX_USAGE')), host=mysqldb_conn.get('host'), port=int(mysqldb_conn.get('port')), user=mysqldb_conn.get('user'), passwd=mysqldb_conn.get('password'), db=mysqldb_conn.get('db'), use_unicode=False, charset=mysqldb_conn.get('charset')) return self.__pool.connection() """ @summary: 释放连接池资源 """ def __exit__(self, type, value, trace): self.cursor.close() self.conn.close()
class DBConnPools(object): __poll = None def __init__(self): pass def __enter__(self): self.coon = self.__getConn() self.cursor = self.coon.cursor() print('创建coon和cursor') return self def __getConn(self, host, port, user, pwd, db, charset): if self.__poll is None: self.__poll = PooledDB( creator=pymysql, mincached=config.DB_MIN_CACHED, maxcached=config.DB_MAX_CACHED, maxshared=config.DB_MAX_SHARED, maxconnections=config.DB_MAX_CONNECYIONS, blocking=config.DB_BLOCKING, maxusage=config.DB_MAX_USAGE, setsession=config.DB_SET_SESSION, host=host, port=port, user=user, passwd=pwd, db=db, use_unicode=True, charset=charset ) return self.__poll.connection() def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.coon.close() print('释放conn和cursor') def get_conn(self, host, port, user, pwd, db, charset): """ 从连接池当中,取出一个连接 """ conn = self.__getConn(host, port, user, pwd, db, charset) return conn
class POOL_DB(object): def __init__(self): self.POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=2, # 连接池允许的最大连接数,0和None表示不限制连接数 mincached=2, # 初始化时,链接池中至少创建的空闲的链接,0表示不创建 maxcached=0, # 链接池中最多闲置的链接,0和None不限制 maxshared=0, # 链接池中最多共享的链接数量,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='192.168.5.105', port=3306, user='******', password='******', database='test_zhengce365_collect', charset='utf8', ) def create_conn(self): conn = self.POOL.connection() cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) return conn, cursor # 关闭连接 def close_conn(self, conn, cursor): cursor.close() conn.close() def close_db(self): self.POOL.close()
class Context(): # 初始化 def __init__(self): # 构建REDIS连接池 self.rds_pool = redis.ConnectionPool(host=REDIS_HOST, password=REDIS_PASSWD, port=REDIS_PORT, db=REDIS_DB) # 构建MYSQL连接池 self.db_pool = PooledDB( creator=MySQLdb, #使用链接数据库的模块 maxconnections=6, #连接池允许的最大连接数,0和None表示没有限制 mincached=2, #初始化时,连接池至少创建的空闲的连接,0表示不创建 maxcached=5, #连接池空闲的最多连接数,0和None表示没有限制 maxshared= 3, #连接池中最多共享的连接数量,0和None表示全部共享,ps:其实并没有什么用,因为pymsql和MySQLDB等模块中的threadsafety都为1,所有值无论设置多少,_maxcahed永远为0,所以永远是所有链接共享 blocking=True, #链接池中如果没有可用共享连接后,是否阻塞等待,True表示等待,False表示不等待然后报错 setsession=[], #开始会话前执行的命令列表 ping=0, #ping Mysql 服务端 检查服务是否可用 host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER, passwd=MYSQL_PASSWD, db=MYSQL_DB, charset='utf8') # 构建NLTK上下文 self.tokenizer = nltk.data.load('tokenizers/punkt/english.pickle') # 获取一个Redis连接 def GetRedis(self): return redis.Redis(connection_pool=self.rds_pool) # 获取一个DB连接 def GetDbConn(self): return self.db_pool.connection()
class db(object): def __init__(self): #初始化连接,指定连接库 yamlPath = "config.yaml" yaml.load(yamlPath, Loader=yaml.BaseLoader) yaml.warnings({'YAMLLoadWarning': False}) f = open(yamlPath, 'r') data = yaml.load(f.read())['st3'] self.pool = PooledDB(pymysql, 5, host=data['db_host'], user=data['db_user'], passwd=data['db_pass'], db=data['db'], port=data['db_port']) def select(self): sql = "select content from sms_logs where id=(select max(id) from sms_logs where mobile='15080605720')" conn = self.pool.connection() cur = conn.cursor() r = cur.execute(sql) r = cur.fetchall() cur.close() conn.close() return r
class MyConnectionPool(object): __pool = None # 创建数据库连接conn和游标cursor def __enter__(self): self.conn = self.__getconn() self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) # 创建数据库连接池 def __getconn(self): if self.__pool is None: self.__pool = PooledDB(creator=config.DB_CREATOR, mincached=config.DB_MIN_CACHED, maxcached=config.DB_MAX_CACHED, maxshared=config.DB_MAX_SHARED, maxconnections=config.DB_MAX_CONNECYIONS, blocking=config.DB_BLOCKING, maxusage=config.DB_MAX_USAGE, setsession=config.DB_SET_SESSION, host=config.DB_TEST_HOST, port=config.DB_TEST_PORT, user=config.DB_TEST_USER, passwd=config.DB_TEST_PASSWORD, db=config.DB_TEST_DBNAME) return self.__pool.connection() # 释放连接池资源 def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() # 从连接池中取出一个连接 def getconn(self): conn = self.__getconn() cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) return cursor, conn
class MyDBPool: """ 连接池类, 使用了单例模式 """ pool_instance = None conn_pool = None def __init__(self): max_connection = 5 self.conn_pool = PooledDB(MySQLdb, max_connection, host=Host, user=User, passwd=Passwd, db=Db, port=Port) @staticmethod def get_instance(): # 可以增加一个参数:根据dbname来加载对应的配置文件 MyDBPool.pool_instance = MyDBPool.pool_instance or MyDBPool() return MyDBPool.pool_instance.get_connect() def get_connect(self): return self.conn_pool.connection()
class PTConnectionPool(object): __pool = None def __enter__(self): self.conn = self.getConn() self.cursor = self.conn.cursor() print("PT数据库创建con和cursor") return self def getConn(self, setting=Setting()): if self.__pool is None: self.__pool = PooledDB(creator=pymysql, mincached=setting.DB_MIN_CACHED, maxcached=setting.DB_MAX_CACHED, maxshared=setting.DB_MAX_SHARED, maxconnections=setting.DB_MAX_CONNECYIONS, blocking=setting.DB_BLOCKING, maxusage=setting.DB_MAX_USAGE, setsession=setting.DB_SET_SESSION, host=setting.MYSQL_HOST, port=setting.MYSQL_PORT, user=setting.MYSQL_USER, passwd=setting.MYSQL_PAWD, db=setting.MYSQL_DB, use_unicode=False, charset=setting.MYSQL_CHARSET) return self.__pool.connection() """ @summary: 释放连接池资源 """ def __exit__(self, type=None, value=None, trace=None): self.cursor.close() self.conn.close() print("PT连接池释放con和cursor")
class SingletonDBPool(object): _instance_lock = threading.Lock() def __init__(self): # print("单例数据库连接初始化") mysql_host = current_app.config.get('MYSQL_HOST') mysql_port = int(current_app.config.get('MYSQL_PORT')) mysql_username = current_app.config.get('MYSQL_USERNAME') mysql_password = current_app.config.get('MYSQL_PASSWORD') mysql_database = current_app.config.get('MYSQL_DATABASE') self.pool = PooledDB(creator=pymysql, maxconnections=50, mincached=2, maxcached=5, maxshared=3, blocking=True, maxusage=None, setsession=[], ping=0, host=mysql_host, port=mysql_port, user=mysql_username, password=mysql_password, database=mysql_database, charset='utf8') def __new__(cls, *args, **kwargs): if not hasattr(SingletonDBPool, "_instance"): with SingletonDBPool._instance_lock: if not hasattr(SingletonDBPool, "_instance"): SingletonDBPool._instance = object.__new__( cls, *args, **kwargs) return SingletonDBPool._instance def connect(self): return self.pool.connection()
class pooldb(object): def __init__(self, ): self.Pool = PooledDB( creator=MySQLdb, #使用链接数据库的模块 maxconnections=0, #连接池允许的最大连接数,0和None表示没有限制 mincached=5, #初始化时,连接池至少创建的空闲的连接,0表示不创建 maxcached=0, #连接池空闲的最多连接数,0和None表示没有限制 maxshared= 0, #连接池中最多共享的连接数量,0和None表示全部共享,ps:其实并没有什么用,因为pymsql和MySQLDB等模块中的threadsafety都为1,所有值无论设置多少,_maxcahed永远为0,所以永远是所有链接共享 blocking=True, #链接池中如果没有可用共享连接后,是否阻塞等待,True表示等待,False表示不等待然后报错 setsession=[], #开始会话前执行的命令列表 ping=0, #ping Mysql 服务端,检查服务是否可用 host='127.0.0.1', port=3306, user='******', passwd='', db='cloudmusic', charset='utf8', cursorclass=MySQLdb.cursors.DictCursor, ) self.connection = self.Pool.connection() self.cursor = self.connection.cursor() def execute(self, sql): count = self.cursor.execute(sql) data = self.cursor.fetchall() self.connection.commit() logging.info(u'fetchall count is:%s' % count) # logging.info(u'查询结果为:%s'%str(data)) self.connection.close() return count, data def close(self, ): self.connection.close()