#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author:      Joson
# @DateTime:    2020/3/6 14:02
# @Description: Pymysql 增删改查操作
# @Version:     1.0

import pymysql

class DataBaseHandle(object):
    """定义MYSQL数据库操作类"""
    def __init__(self):
        """初始化数据库信息创建连接"""
        self.host = 'host'
        self.username = 'root'
        self.password = '123456'
        self.database = 'dbname'
        self.port = 3306
        self.db = pymysql.connect(self.host,self.username,self.password,self.database,self.port,charset='utf8')

    def insertDB(self,sql):
        """插入数据"""
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as err:
            print('insert data error:',err)
            self.db.rollback() # 发生错误时回滚
        finally:
            self.cursor.close()

    def insertListDB(self,table,dataList):
        """批量插入列表数据
        Params:
            table:插入数据的表名称
            dataList:数据列表 [{key:value,}{key:value,},...]
        """
        self.cursor = self.db.cursor()
        cols = ', '.join('`{}`'.format(k) for k in dataList[0].keys())
        val_cols = ', '.join('%({})s'.format(k) for k in dataList[0].keys())
        sql = 'INSERT INTO {}(%s) values(%s)'.format(table)

        res_sql = sql % (cols, val_cols)
        try:
            self.cursor.executemany(res_sql, dataList)  # 将字典列表传入
            self.db.commit()
        except Exception as err:
            print('insert many data error:', err)
            self.db.rollback()
        finally:
            self.cursor.close()

    def deleteDB(self,sql):
        """删除数据"""
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as err:
            print('delete data error:',err)
            self.db.rollback()
        finally:
            self.cursor.close()

    def updateDB(self,sql):
        """修改数据"""
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as err:
            print('update data error:',err)
            self.db.rollback()
        finally:
            self.cursor.close()

    def selectDB(self,sql):
        """查询数据"""
        self.cursor = self.db.cursor() # 以元组格式返回查询结果
        # self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor) # 查以字典格式返回查询结果
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
        except Exception as err:
            data = tuple()
            print('select data error:',err)
        finally:
            self.cursor.close()
        return data

    def closeDB(self):
        """关闭数据库连接"""
        self.db.close()

if __name__ == '__main__':
    DbHandle = DataBaseHandle()
    # DbHandle.insertDB('INSERT INTO minitor(id,used_order) VALUES ("999","321")')
    # DbHandle.deleteDB('DELETE FROM minitor WHERE id="999"')
    # DbHandle.updateDB('UPDATE minitor SET used_order="888" WHERE id="999"')
    # data = DbHandle.selectDB('SELECT * FROM minitor WHERE id="999" LIMIT 10')
    # dataList = [{'id':123,'used_order':777},{'id':789,'used_order':555}]
    # DbHandle.insertListDB('minitor',dataList)
    DbHandle.closeDB()