python連接配接mysql中沒有長連接配接的概念,這邊利用mysql的ping機制,來實作長連接配接功能,python mysql的ping函數會校驗連接配接的可用性,如果連接配接不可用将會産生異常,利用這一特性,構造一個連接配接丢失的循環,不斷嘗試連接配接資料庫,直到連接配接恢複,使用這樣的機制不需要關閉資料庫功能。轉載
import sys,MySQLdb,traceback
import time
class mysql:
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.charset= charset
self.conn = None
self._conn()
def _conn (self):
try:
self.conn = MySQLdb.Connection(self.host, self.user, self.passwd, self.db, self.port , self.charset)
return True
except :
return False
def _reConn (self,num = 28800,stime = 3): #重試連接配接總次數為1天,這裡根據實際情況自己設定,如果伺服器當機1天都沒發現就......
_number = 0
_status = True
while _status and _number <= num:
try:
self.conn.ping() #cping 校驗連接配接是否異常
_status = False
except:
if self._conn()==True: #重新連接配接,成功退出
_status = False
break
_number +=1
time.sleep(stime) #連接配接不成功,休眠3秒鐘,繼續循環,知道成功或重試次數結束
def select (self, sql = ''):
try:
self._reConn()
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
self.cursor.execute (sql)
result = self.cursor.fetchall()
self.cursor.close ()
return result
except MySQLdb.Error,e:
#print "Error %d: %s" % (e.args[0], e.args[1])
return False
def select_limit (self, sql ='',offset = 0, length = 20):
sql = '%s limit %d , %d ;' % (sql, offset, length)
return self.select(sql)
def query (self, sql = ''):
try:
self._reConn()
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
self.cursor.execute ("set names utf8") #utf8 字元集
result = self.cursor.execute (sql)
self.conn.commit()
self.cursor.close ()
return (True,result)
except MySQLdb.Error, e:
return False
def close (self):
self.conn.close()
if __name__=='__main__':
my = mysql('localhost','root','password','database',3306)
print my.select_limit('select * from sdb_admin_roles',1,1)
#my.close()