import pymysql
from DBUtils.PooledDB import PooledDB
# python连接mysql，建议用连接池，如果只是pymysql普通连接的话，同时执行两个或两个以上的sql语句会报错
class DB(object):
"""
数据库
"""
def __init__(self):
# 创建连接池对象
self.pool = PooledDB(
pymysql,
maxconnections=8, # 最大连接数
mincached=2,
host='127.0.0.1',
port=3306,
user='feng',
password='87654321',
database='test',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # 查询出来的数据为字典格式
)
def create_connect(self):
"""
创建sql连接
:return:
"""
connect = self.pool.connection() # 建立一个连接， 我的理解是从连接池里取出来一个连接
cursor = connect.cursou() # 创建游标对象
return connect, cursor
def close_connect(self, connect, cursor):
"""
关闭连接
:return:
"""
cursor.close() # 关闭游标对象
connect.close() # 关闭连接
def query(self):
"""
查询
:return:
"""
# connect = self.pool.connection() # 建立一个连接， 我的理解是从连接池里取出来一个连接
# cursor = connect.cursou() # 创建游标对象
connect, cursor = self.create_connect()
# 简单查询
sql = 'select username from user_info;'
cursor.execute(sql)
result = cursor.fetchall() # fetchall查询全部,返回的是一个列表/fetchone查询单条，返回的是一个字典/fetchmany(5),查询多个，可以指定查询数量
# 条件查询指定字段
sql = 'select id,username,status,userLevel,createDate,modifyDate from user_info where username="%s";' % 'laowang'
# 按指定字段分页查询，倒序排序
sql = 'select id,userLevel,username,status,createDate,modifyDate from user_info order by createDate desc limit %d, %d;' % ((1 - 1) * 10, 10)
# 按指定字段分页查询，倒序排序
sql = 'select id,userLevel,username,status,createDate,modifyDate from user_info order by createDate desc limit %d, %d;' % ((1 - 1) * 10, 10)
# 关联查询
sql = 'select pro_per.project_id,pro_per.office,per.personnelName from (project_personnel pro_per inner join project_info pro on pro_per.project_id=pro.id) inner join personnel per on pro_per.personnel_id=per.id where pro_per.responsible_status=1;'
# 模糊查询
sql = 'select * from project_info where project like "%%%%%s%%%%" order by createDate desc limit %d, %d;' % ('测试', (1 - 1) * 10, 10)
# 根据日期查询
sql = "select * from commission where date_format(commissionDate, '%Y-%m')='{}'".format('2020-10-25')
# 查询数量
sql = 'select count(*) from project_personnel where personnel_id="%d"' % 22
# cursor.close() # 关闭游标对象
# connect.close() # 关闭连接
self.close_connect(connect, cursor) # 程序执行完关闭连接，相当于把这个连接再放回连接池
def add_to(self):
"""
添加
:return:
"""
# connect = self.pool.connection() # 建立一个连接， 我的理解是从连接池里取出来一个连接
# cursor = connect.cursou() # 创建游标对象
connect, cursor = self.create_connect()
sql = 'insert into personnel(personnelName, status, office) values ("%s", "%d", "%d");' % ('老张', 1, 1)
cursor.execute(sql)
connect.commit()
self.close_connect(connect, cursor)
def update(self):
"""
更新
:return:
"""
connect, cursor = self.create_connect()
sql = 'update personnel set status="%d" where id="%d";' % (1, 2)
cursor.execute(sql)
connect.commit()
self.close_connect(connect, cursor)
