python3编程使用python-mysql-connector对数据库进行增删查改操作。但每次连接mysql请求时,都是独立的去请求访问,比较浪费资源,而且访问数量达到一定数量时,对mysql的性能会产生较大的影响。因此实际使用中,通常会使用数据库的连接池技术,来访问数据库达到资源复用。
目前python3已不支持Mysqldb,本文使用mysql.connector.pool对连接池进行管理。直接上代码,自己在网上找的相关材料,总结而成。
#coding=utf-8
'''
Created on 2018年3月7日
功能:利用mysql.connector.pooling创建mysql连接池
@author: Administrator
'''
import mysql.connector.pooling
import logging
import db_config
class MysqlPool:
#def __init__(self,user,passwd,host,database,poolsize):
def __init__(self,poolsize):
'''db_config={
'user':user,
'passwd':passwd,
'database':database,
'host':host
}
'''
try:
self.pool=mysql.connector.pooling.MySQLConnectionPool(pool_size=poolsize,pool_reset_session=True, **db_config.db_config)
except Exception as e:
logging.warn(e)
def executeSQL(self,sql):
try:
conn=self.pool.get_connection()
cursor=conn.cursor()
cursor.execute(sql)
cursor.close()
conn.commit()
logging.info('执行成功')
except Exception as e:
logging.warn(e)
finally:
if conn:
conn.close()
def executeQuery(self,sql):
result=None
try:
conn=self.pool.get_connection()
cursor=conn.cursor()
cursor.execute(sql)
result=cursor.fetchall()
cursor.close()
conn.commit()
logging.info('查询成功')
except Exception as e:
logging.warn(e)
finally:
if conn:
conn.close()
return result
def logger():
logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
if __name__ == '__main__':
logger()
#pool=MysqlPool(user='root',passwd='mysql',database='demo',host='localhost',poolsize=5)
pool=MysqlPool(poolsize=5)
#测试更新语句
sql="update substation set subname='葛洲坝' where id=1"
pool.executeSQL(sql)
#测试查询语句
sql='select * from substation where id=1'
re=pool.executeQuery(sql)
print(re)
这里我把数据库的配置文件(db_config.py)单独摘出来了,更有利于模块化管理。内容如下:
用户名,密码替换成自己的就行了。
#encoding=utf-8
'''
Created on 2018.03.07
@author: Administrator
'''
db_config={
'user':'root',
'passwd':'mysql',
'database':'demo',
'host':'localhost'
}