[转] MySQLdb进一步封装，解决连接断开问题

来源：互联网发布：知柏地黄丸的功效编辑：程序博客网时间：2024/06/08 21:20

本文转自：http://blog.csdn.net/wzm112/article/details/7745835

Python链接MySQL中没有长链接的概念，但我们可以利用mysql的ping机制，来实现长链接功能~
思路:

    1 python mysql 的cping 函数会校验链接的可用性,如果连接不可用将会产生异常    2 利用这一特性，构造一个连接丢失的循环，不断尝试连接数据库，直到连接恢复    3 使用这样的机制不需要关闭数据库功能,对于驻留进程，有大量数据进行写操作时，很有用途

#!/usr/bin/env python  # -*-coding:UTF-8-*-  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()

0 0