由于工作需要写了一个小脚本 ，需要跨库查询数据和接口中的数据做对比 ，并输出到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还没实现自动比对数据，只是记录一个实现方式。供大家参考
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
