python连接mysql中没有长连接的概念，但我们可以利用mysql的ping机制，来实现长连接功能~
思路:
1 python mysql 的cping 函数会校验连接的可用性,如果连接不可用将会产生异常
2 利用这一特性，构造一个连接丢失的循环，不断尝试连接数据库，直到连接恢复
3 使用这样的机制不需要关闭数据库功能,对于驻留进程，有大量数据进行写操作时，很有用途
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
#!/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() |