python 跨库查询（同一实例不同数据库）和接口数据进行对比，并把结果输出到Excel中

由于工作需要写了一个小脚本 ，需要跨库查询数据和接口中的数据做对比 ，并输出到Excel中

思路：

1 最笨的办法，把跨库查询的sql 拆成两部分，先查出固定的数据（做参数的数据）

2 另外数据库和接口数据查出来放到Excel中

不废话  上代码

查询第一个数据库  获取参数数据

#查询bch_thread_leju_com
class BchThread(object):
    def __init__(self, db='xxx', user='xxx', passwd='xxx',
                 host='xxx', port=xxx, charset='utf8'):
        # 初始化数据库
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        self.__cursor = None

    def _connect_db(self):
        #dbManager._connect_db()连接数据库
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = pymysql.connect(**params)
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        # dbManager._close_db()关闭数据库
        self.__cursor.close()
        self.__connect.close()

    #查询
    def get(self, sql):
        self._connect_db()
        self.__cursor.execute(sql)
        result = self.__cursor.fetchall()
        jsonData = []
        for row in result:
            result = {}
            result = row
            jsonData.append(result)
        #print (u'转换为列表字典的原始数据：',jsonData)
        self._close_db()
        return  jsonData

查询第二个数据库

lass BchDatacenter(object):
    def __init__(self, db='xxx', user='xxx', passwd='xxx',
                 host='xxx', port=xxx, charset='utf8'):
        # 初始化数据库
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        self.__cursor = None

    def _connect_db(self):
        #dbManager._connect_db()连接数据库
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = pymysql.connect(**params)
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        # dbManager._close_db()关闭数据库
        self.__cursor.close()
        self.__connect.close()

    #查询
    def get(self, sql):
        self._connect_db()
        self.__cursor.execute(sql)
        result_tup = self.__cursor.fetchall()
        result_str = str(result_tup)
        #正则取出数字
        result_str = re.findall("\d+", result_str)
        # 去掉中括号
        ss = list(result_str)
        for x in range(len(ss)):
            if ss[x] == '[':
                ss[x] = "'"
            if ss[x] == ']':
                ss[x] = "'"
            result_str = ''.join(ss)
        self._close_db()
        return  result_str

 查询接口数据

class ApiData(object):

    def apidata(self,yhd_time,timeStamp):
        data_input = yhd_time
        timestamp = timeStamp
        url = "xxx"
        payload = {'appid': 'xxx',
                   'filter': '[{{"name":"date","type":"eq","value":"{data_input}"}}]'.format(data_input=data_input),
                   'timestamp': '{timestamp}'.format(timestamp=timestamp),
                   'dataType': '8'}
        files = []
        headers = {'Cookie': 'xxx'}

        response = requests.request("POST", url, headers=headers, data=payload, files=files)
        #print(response.text)
        return (response.text)

操作excel

#操作Excel
class toExcel(object):

    def xw_toExcel(self, data, fileName):  # xlsxwriter库储存数据到excel
        workbook = xw.Workbook(fileName)  # 创建工作簿
        worksheet1 = workbook.add_worksheet("sheet1")  # 创建子表
        worksheet1.activate()  # 激活表
        title = ['数据来源','时间','广告使用量(分发广告量)', '直销使用量 (分发直销量)', '分发来客量', '来客接单量']  # 设置表头
        worksheet1.write_row('A1', title)  # 从A1单元格开始写入表头
        i = 2  # 从第二行开始写入数据
        for j in range(len(data)):
            insertData = [data[j]["source"], data[j]["data"], data[j]["ad_count"], data[j]["zx_count"],data[j]["laike_count"],data[j]["laike_order_count"]]
            row = 'A' + str(i)
            worksheet1.write_row(row, insertData)
            i += 1
        workbook.close()  # 关闭表
        print("导出成功")

 main 方法里

if __name__ == '__main__':
    indata = input("请输入查询的日期（格式为2022-01-19 23:59:59）：")
    #截取年月日
    yhd_time= indata[0:10]
    #print(indata[0:10])
    #字符串时间转换时间戳
    timeArray = time.strptime(indata, "%Y-%m-%d %H:%M:%S")
    # 转换为时间戳
    timeStamp = int(time.mktime(timeArray))




    #查询行为表 source_id=74的行为id
    jsonData = BchThread().get('SELECT id FROM   bch_th where  bch_th.source_id=74 ')
    #进行json格式化编码
    jsondatar = json.dumps(jsonData, ensure_ascii=False)
    # 去除首尾的中括号   <class 'str'>
    strData = jsondatar[1:len(jsondatar) - 1]
    #去掉中括号
    ss = list(strData)
    for x in range(len(ss)):
        if ss[x] == '[':
            ss[x] = "'"
        if ss[x] == ']':
            ss[x] = "'"
        strData = ''.join(ss)

    sql2="select count(1) as count   from xxx  where distribute_channel=100002 and status=3  AND add_time <={0} and user_sid in ({1})"
    sql = sql2.format(timeStamp, strData)
    ad_count = BchDatacenter().get(sql)
    print('数据库——广告使用量(分发广告量):'+ad_count)
    sql2 = "SELECT count(*) FROM where  distribute_channel in (100046,1000047)  AND   add_time <={0}     and user_sid in ({1})"
    sql = sql2.format(timeStamp, strData)
    zx_count = BchDatacenter().get(sql)
    print('数据库——直销使用量 (分发直销量):' + zx_count)
    sql2 = "SELECT count(*) FROM xxx.dc_dwd_laike bch_laike JOIN xxx.dc_dwd_distribute bch_dwd  ON bch_laike.dis_id=bch_dwd.dis_id  where  bch_dwd.distribute_channel in (5,6,7,100049,100051,100052) AND   bch_laike.first_push_time <= {0}  and user_sid in ({1})"
    sql = sql2.format(timeStamp, strData)
    laike_count = BchDatacenter().get(sql)
    print('数据库——分发来客量:' + laike_count)
    sql2 = "SELECT count(*) FROM xxx.dc_dwd_laike bch_laike JOIN xxx.dc_dwd_distribute bch_dwd  ON bch_laike.dis_id=bch_dwd.dis_id  where  bch_dwd.distribute_channel in (5,6,7,100049,100051,100052)  AND  last_order_time>0 AND   bch_laike.first_push_time <= {0}  and user_sid in ({1})"
    sql = sql2.format(timeStamp, strData)
    laike_order_count = BchDatacenter().get(sql)
    print('数据库——来客接单量:' + laike_order_count)
    # 写入Excel
    #w_excel(strData)

    # "-------------数据用例-------------"




    #调用接口数据
    apidata = ApiData().apidata(yhd_time,timeStamp)
    data = json.loads(apidata)
    #遍历字典 取data 数值  遍历完数据是列表<class 'list'>
    a=[]
    for key ,v  in data.items():
        #print(key,'=',v)
        if key=='data':
            a = v
            #print(type(a))
    #列表转换为字典格式  <class 'dict'>
    d1 = dict()
    for i in a:
        d1=i
    # 获取到单个数值
    ae=''
    aa=''
    yy=''
    ii=''
    for key1, v1 in d1.items():
    # print(key,'=',v)
        if key1 == 'ad_count':
            ae = v1
        elif key1 == 'zx_count':
            aa = v1
        elif key1 == 'laike_count':
            yy = v1
        elif key1 == 'laike_order_count':
            ii = v1


    sd=str(ae)
    #print(type(sd))
    print('接口——广告使用量(分发广告量):' + sd)
    su=str(aa)
    print('接口——直销使用量 (分发直销量):' + su)
    si = str(yy)
    print('接口——分发来客量:' + si)
    sp = str(ii)
    print('接口——来客接单量:' + sp)

    #导出Excel 数据
    testData = [
            {"source": "数据库", "data": yhd_time,  "ad_count": ad_count, "zx_count": zx_count, "laike_count": laike_count,"laike_order_count": laike_order_count},
            {"source": "接口",  "data": yhd_time, "ad_count": sd, "zx_count": su, "laike_count": si,"laike_order_count": sp},

    ]
    fileName = 'D:\再回访可视化数据支持——测试数据库和接口对比.xlsx'
    toExcel().xw_toExcel(testData,fileName)

缺陷：1 脚本代码写的很粗糙，还没有分层 ，只要记录个思路和实现方式，不喜勿喷

2还没实现自动比对数据，只是记录一个实现方式。供大家参考