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