requests+unittest接口自动化-mysql数据库类封装

文件路径：项目目录/common/mysql_handler.py

config.ini文件下数据库配置

mysql_handler.py

import pymysql
import json

from common.config_handler import conf

class OperateMysql(object):
    def __init__(self):
        # 数据库初始化连接

        self.conn = pymysql.connect(
            host=conf.get("mysqlconf_test_publication_house", "host"),
            user=conf.get("mysqlconf_test_publication_house", "user"),
            password=conf.get("mysqlconf_test_publication_house", "password"),
            database=conf.get("mysqlconf_test_publication_house", "db_name_base"),
            charset="utf8mb4",
            cursorclass=pymysql.cursors.DictCursor
        )

        # 创建游标操作数据库
        self.cur = self.conn.cursor()

    def select_first_data(self, sql):
        """
        查询第一条数据
        """
        try:
            # # 提交，防止事务冲突
            # self.conn.commit()

            # 执行 sql 语句
            self.cur.execute(sql)
        except Exception as e:
            print("执行sql异常:%s" % e)
        else:
            # 获取查询到的第一条数据
            first_data = self.cur.fetchone()
            # print(first_data)
            # 将返回结果转换成 str 数据格式，禁用acsii编码
            first_data = json.dumps(first_data, ensure_ascii=False)
            return first_data

    def select_all_data(self, sql):
        """
        查询结果集
        """
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("执行sql异常:%s" % e)
        else:
            first_data = self.cur.fetchall()
            first_data = json.dumps(first_data, ensure_ascii=False)
            return first_data

    def get_count(self, sql):
        """
        获取数据条数
        """
        self.conn.commit()
        return self.cur.execute(sql)

    def del_data(self, sql):
        """
        删除数据
        """
        res = {}
        try:
            # 执行SQL语句
            result = self.cur.execute(sql)
            # print(result)
            if result != 0:
                # 提交修改
                self.conn.commit()
                res = {'删除成功'}
            else:
                res = {'没有要删除的数据'}
        except:
            # 发生错误时回滚
            self.conn.rollback()
            res = {'删除失败'}
        return res

    def update_data(self, sql):
        """
        修改数据
        """
        try:
            self.cur.execute(sql)
            self.conn.commit()
            res = {'更新成功'}
        except Exception as e:
            self.conn.rollback()
            res = {'更新删除'}
        return res

    def insert_data(self, sql, data):
        """
        新增数据
        """

        try:
            self.cur.execute(sql, data)
            self.conn.commit()
            res = {data, '新增成功'}
        except Exception as e:
            res = {'新增失败', e}
        return res

    def conn_close(self):
        # 关闭数据库
        self.cur.close()
        self.conn.close()


if __name__ == "__main__":

    om = OperateMysql(dbname="test_publication_house_base")
    sql = "select app_id,app_secret from company where pk_company='dada0a87-b700-4bf2-aa24-d884611c53d0'"
    one_data = om.select_first_data(sql)
    print(one_data)
    # 关闭数据库
    om.conn.close()