一、背景
年底了，运营同学要做报告，于是来找我要数据，而数据来源于MySQL数据库的两个表，两个表中的数据无法一一对应，数据未能通过SQL直接查询出来，需要将表1中的查询结果传参到表2的查询SQL的条件中。数据量较大，若将查询结果复制到excel中，之后再去一一手动匹配的话，这工作量也是巨大的。于是尝试使用Python来实现
二、实现步骤
数据库查询操作
使用pymysql库连接Mysql数据库查询数据，若是初次使用，需要安装后再导入
pip install pymysql连接数据库查询示例代码如下：
from pprint import pprint
import pymysql
def select_from_mysql(dbinfo, sql):
"""
连接数据库执行sql
"""
conn = pymysql.connect(**dbinfo)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(sql)
results = cursor.fetchall()
conn.close()
return results
if __name__ == "__main__":
# 数据库信息
db_info = {
'host': "192.168.1.1",
'port': 3306,
'user': "test",
'passwd': "test",
'db': "test_db",
'charset': "utf8"
}
sql = "select * from test_table;"
r = select_from_mysql(db_info, sql)
pprint(r)运行效果：
写入表格表头
使用xlwt模块操作excel，若未使用过xlwt，需要先安装导入
pip install 写入表头示例代码：
import xlwt
def to_excel():
# 定义表头
headers = ['主业务id', '次业务id', '会员id', '是否完成', '进入时间', '分值', '是否重复操作', '重复操作次数', '是否首次操作', '失败次数', '总时长']
# 新建工作簿和工作表
book = xlwt.Workbook(encoding='utf-8') # 新建工作簿
sheet = book.add_sheet("new_sheet", cell_overwrite_ok=True) # 新建sheet
# 写入表头
for col, val in enumerate(headers):
sheet.write(0, col, val)
# 保存excel
book.save("test1.xlsx")
if __name__ == "__main__":
to_excel()
写入表格内容
从表1中查询数据，批量写入excel，根据指定的条件，从表2中查询数据，批量写入excel
import pymysql
import xlwt
def select_from_mysql(dbinfo, sql):
"""
连接数据库执行sql
"""
conn = pymysql.connect(**dbinfo)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(sql)
results = cursor.fetchall()
conn.close()
return results
def to_excel(dbinfo):
# 定义表头
headers = ['主业务id', '次业务id', '会员id', '是否完成', '进入时间', '分值', '是否重复操作', '重复操作次数', '是否首次操作', '失败次数', '总时长']
# 新建工作簿和工作表
book = xlwt.Workbook(encoding='utf-8') # 新建工作簿
sheet = book.add_sheet("new_sheet", cell_overwrite_ok=True) # 新建sheet
# 写入表头
for col, val in enumerate(headers):
sheet.write(0, col, val)
# 查询表1的数据
a_ids = "197715879100678144,174809847437459456"
# sql1 = "select activity_ident,mission_ident,member_id,success,created_at,point,(case when reply_count>0 then 1 " \
# "else 0 end) as is_replay, reply_count,failure_before_success_count,total_failure_count from " \
# f"activity_stat_member_activity where activity_ident in ({a_ids}) and member_id={103755}; "
sql1 = f"select * from table1 where a_id in ({a_ids}) and m_id={100001}"
results1 = select_from_mysql(dbinfo, sql1)
# 根据表1查询结果查询并组装表2的数据
if results1 is not None:
for i, v in enumerate(results1):
r1_k_list = [key for key in v]
for col, k in enumerate(v):
if col == 1:
sheet.write(i + 1, col, v[r1_k_list[col]])
# 查询表2的数据
# sql2 = "select sum(t1.time) sum_time,t1.mis_id from (select round(max(created_at)-min(created_at)) " \
# "time,max(mission_ident) mis_id from activity_member_mission_node_record where activity_ident " \
# f"={v[r1_k_list[0]]} and mission_ident={v[r1_k_list[col]]} and member_id={103755} " \
# "group by mission_record_ident) t1 group by t1.mis_id;"
sql2 = f"select * from table2 where a_id={v[r1_k_list[0]]} and mi_id={v[r1_k_list[col]]} and m_id={100001}"
results2 = select_from_mysql(dbinfo, sql2)
if results2 != ():
r2_k_list = [key for key in results2[0]]
sheet.write(i + 1, 10, results2[0][r2_k_list[0]])
sheet.write(i + 1, 11, results2[0][r2_k_list[1]])
elif col in [3, 6]:
if v[r1_k_list[col]] == 1:
sheet.write(i + 1, col, '是')
else:
sheet.write(i + 1, col, '否')
elif col == 8:
if v[r1_k_list[3]] == 1 and v[r1_k_list[col]] == 0:
sheet.write(i + 1, col, '是')
else:
sheet.write(i + 1, col, '否')
else:
sheet.write(i + 1, col, v[r1_k_list[col]])
# 保存excel
book.save("test1.xlsx")
if __name__ == "__main__":
# 数据库信息
db_info = {
'host': "192.168.1.1",
'port': 3306,
'user': "test",
'passwd': "test",
'db': "test_db",
'charset': "utf8"
}
to_excel(db_info)运行效果：
三、总结
从最后的运行结果来看，已经可以成功根据指定条件将数据批量写入到excel中，但是发现excel中的数据展示并不正常，这是接下来我们要解决的问题
1.写入到excel中数据格式问题
2.excel表格样式问题