pymysql可以配合线程池使用,以达到控制数据库链接,以及减少频繁的建立和销毁链接的过程,提升效率
具体代码:
import pymysql
from DBUtils.PooledDB import PooledDB,SharedDBConnection
class mysql_conn_poll(object):
def __init__(self,flag=1):
if flag == 0:
self.user='root'
self.password='123456'
self.host='127.0.0.1'
self.port=3306
self.database='test'
self.pool=PooledDB(creator=pymysql,maxconnections=50,mincached=0,maxcached=10,maxshared=0,blocking=True,
maxusage=None,setsession=[],ping=0,host=self.host,user=self.user,password=self.password,
database=self.database,port=self.port,charset='utf8')
def __new__(cls, *args, **kwargs):
'''
单例模式
:param args:
:param kwargs:
:return:
'''
if not hasattr(cls,'_instance'):
cls._instance=object.__new__(cls)
return cls._instance
def sql_cursor(self,type=0):
'''
启动链接
:return:
'''
conn=self.pool.connection()
if type == 0:
cursor = conn.cursor()
else:
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return conn,cursor
def connect_close(self,conn,cursor):
'''
关闭链接
:param conn:
:param cursor:
:return:
'''
cursor.close()
conn.close()
def sql_execu(self,sql=None,data=None,type=0):
'''
:param sql:需要执行的sql
:param data: sql语句中的参数
:param type: 执行返回的数据格式 0 默认元组,1 默认字典
:return: 只返回一条
'''
conn,cursor = self.sql_cursor(type=type)
cursor.execute(sql, data)
ret = cursor.fetchone()
self.connect_close(conn,cursor)
return ret
def sql_execus(self, sql=None, data=None, type=0):
'''
:param sql:需要执行的sql
:param data: sql语句中的参数
:param type: 执行返回的数据格式 0 默认元组,1 默认字典
:return: 返回所有的数据
'''
conn, cursor = self.sql_cursor(type=type)
cursor.execute(sql, data)
ret = cursor.fetchall()
self.connect_close(conn, cursor)
return ret
def sql_DML(self, sql=None, data=None, type=0):
'''
增删改操作,单条
:param sql:
:param data:
:return:
'''
conn,cursor = self.sql_cursor(type=type)
cursor.execute(sql, data)
conn.commit()
self.connect_close(conn, cursor)
return True
def sql_DMlS(self,sql=None,data=None,type=0):
'''
增删改 操作 多条
:param sql:
:param data:
:return:
# sql = 'insert into userinfo (username,password) values (%s,%s)'
# data = [
# ('xxoo', '789'),
# ('ooxx', '456'),
# ('xoxo', '123')
# ]
# # executemany方法 执行多条
# cursor.executemany(sql, data)
# conn.commit()
'''
conn,cursor = self.sql_cursor(type=type)
cursor.executemany(sql, data)
conn.commit()
cursor.close()
self.connect_close(conn, cursor)
return True