##起因
最近处理数据库里的数据,有余数据太大,不可能采用短连接,只能采用长连接了。所以写了个这个
参考资料
https://www.polarxiong.com/archives/Python-mysql-connector中获取数据库值的一些问题.html
https://blog.csdn.net/wzm112/article/details/7745835
from mysql import connector
import time
class mysql_connect:
def __init__(self,
host='',
user='',
passwd='',
db='',
port=3306,
charset='utf8mb4'
):
self.host = host
self.user = user
self.passwd = passwd
self.db = db
self.port = port
self.charset = charset
self.conn = None
self.cursor = None
self._conn()
def _conn(self):
try:
self.conn = connector.connect(host=self.host,
user=self.user,
password=self.passwd,
database=self.db,
port=self.port)
return True
except:
return False
def _cursor(self):
self.cursor = self.conn.cursor(dictionary=True)
self.cursor.execute("set names '{}'".format(self.charset))
def _free(self):
self.cursor.close()
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.cursor.execute(sql)
result = self.cursor.fetchall()
self._free()
return result
except Exception as e:
# print "Error %d: %s" % (e.args[0], e.args[1])
return False
def query(self, sql=''):
try:
self._reConn()
self._cursor()
result = self.cursor.execute(sql)
self.conn.commit()
self._free()
return (True, result)
except Exception as e:
return False
def close(self):
self.conn.close()
if __name__ == "__main__":
conn = mysql_connect(host="localhost",
user="root",
passwd="root",
db="test")
data = conn.select("select id from shop_customer limit 10")
print(data)