python 长连接数据库
python链接mysql中没有长链接的概念，但我们可以利用mysql的ping机制，来实现长链接功能
思路:
1 python mysql 的cping 函数会校验链接的可用性,如果连接不可用将会产生异常
2 利用这一特性，构造一个连接丢失的循环，不断尝试连接数据库，直到连接恢复
3 使用这样的机制不需要关闭数据库功能,对于驻留进程，有大量数据进行写操作时，很有用途
#!/usr/bin/env python
# -*-coding:UTF-8-*-
import MySQLdb
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 handle (self, sql = ''):
try:
self._reConn()
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
self.cursor.execute ("set names utf8") #utf8 字符集
self.cursor.execute (sql)
self.conn.commit()
self.cursor.close ()
return True
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
return False
def close (self):
self.conn.close()
if __name__=='__main__':
my = mysql('localhost','user','passwd','test',3306)
my.handle('create table test(id int,name varchar(10))default charset=utf8')
my.handle('insert into test values(1,"tom")')
print my.select('select * from test')
#my.close()
