mysql 操作

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)