python查询mysql并生成excel表
需求说明
开发不愿意单独为某个项目做后台
并且运营那边需要合并多个表的数据
因此找上了我。
要求每周执行一次。月初也执行一次
要查询2个mysql数据库多个表并生成excel表
我的想法
找开发要sql语句。同时要求说明查询结果如何填充excel表
把程序打成exe的。放在某个可以连接数据库的windows机器上。通过计划任务定期执行
同时安装一个nginx作为http文件服务器。再做个简单认证。让运营去打开下载即可
代码实现
import datetime,pymysql,time
import xlwt
mysql_host='172.16.203.1'
#mysql_host='10.0.2.17'
mysql_port=13306
#mysql_port=3306
mysql_db1='tzqweb'
mysql_db2='password' #这里输入真实密码
mysql_user='system'
mysql_pass='password' #这里输入真实密码
#上一个月的第一天日期2018-09-01这种格式
if datetime.date.today().month==1:
LastMonthFirstDay = datetime.date(datetime.date.today().year-1,datetime.date.today().month+11,1)
else:
LastMonthFirstDay = datetime.date(datetime.date.today().year,datetime.date.today().month-1,1)
#上一个月的最后日期2018-09-01这种格式
LastMonthLastDay = datetime.date(datetime.date.today().year,datetime.date.today().month,1)-datetime.timedelta(1)
#获取这个月第一天2018-09-01这种格式
ThisMonthFirst_day=datetime.date.today().replace(day=1)
#获取今天2018-09-01这种格式
ThisMonthToday=datetime.date.today()
##得到这个月的sql--1
print('sql----------------1,返回值为8列')
#获取的值为8列个数字，第一个是日期，第2个是新用户访问，第3个是点击人数，第4个是点击次数，5是包月人数，6是包月失败，7老包月，8点播人数
SQL1='''select tab1.time1,ifnull(s4.zcrs,0) as zcrs ,ifnull(s1.djrs,0) as djrs ,ifnull(s1.djcs,0) as djl ,ifnull(s2.byrs,0) as byrs ,ifnull(s3.sbrs,0) as dgsbrs,ifnull(tab2.lbyfwrs,0) as lbyfwrs,ifnull(s5.dbrs,0) as dbrs from
(SELECT CONCAT(DATE_FORMAT(curdate(),'%Y-%m-'),aa.stats_day) time1 FROM mydate aa WHERE CAST(aa.stats_day AS SIGNED)<=DAYOFMONTH(last_day(curdate()))) tab1
left join
(select s1.time1 as time1,count(DISTINCT o.userid) as lbyfwrs from
(SELECT CONCAT(DATE_FORMAT(curdate(),'%Y-%m-'),aa.stats_day) time1 FROM mydate aa WHERE CAST(aa.stats_day AS SIGNED)<=DAYOFMONTH(last_day(curdate()))) s1,
edu_userorder o,edu_userapp a
where o.appid=188 and o.ordertime<=CONCAT(DATE_ADD(s1.time1,interval -1 day),' 23:59:59')
and a.userid=o.userid and a.appid=188 and a.recordtime<=CONCAT(s1.time1,' 23:59:59')
and a.recordtime>=CONCAT(s1.time1,' 00:00:00')
group by s1.time1) tab2
on tab1.time1=tab2.time1
left join
(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time1,count(*) as djcs,count(distinct t.userid) as djrs from edu_userapp t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188
group by DATE_FORMAT(t.recordtime,'%Y-%m-%d') )s1
on tab1.time1=s1.time1 left join
( select DATE_FORMAT(t.ordertime,'%Y-%m-%d') as time2, count(distinct t.userid) as byrs from edu_userorder t where t.ordertime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and t.ordertime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188
group by DATE_FORMAT(t.ordertime,'%Y-%m-%d')) s2
on tab1.time1=s2.time2 left join
(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time2, count(distinct t.userid) as sbrs from edu_userorder_fail t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188 and t.errorcode='9314'
group by DATE_FORMAT(t.recordtime,'%Y-%m-%d')) s3
on tab1.time1=s3.time2 left join
(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time1,count(*) as zcrs from (
select a.userid,min(a.recordtime) as recordtime from edu_userapp a where a.appid=188 group by a.userid
) t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59')
group by DATE_FORMAT(t.recordtime,'%Y-%m-%d') )s4
on tab1.time1=s4.time1 left join
(
select DATE_FORMAT(bl.starttime,'%Y-%m-%d') as time1,count(DISTINCT bl.userid) as dbrs from seewomq.edu_userlogs bl
where bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')
and bl.platform='ttmq'
group by DATE_FORMAT(bl.starttime,'%Y-%m-%d')
)s5
on tab1.time1=s5.time1
order by tab1.time1;'''
#获取的值为2个数字，第一个是点击人数，第二个是点击次数
SQL2='''select count(DISTINCT a.userid) as zdjrs ,count(*) zdjl from edu_userapp a
where a.appid=188 and a.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and a.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00') ;
'''
#获取的值为1个数字,值为点播人数
SQL3='''select count(DISTINCT bl.userid) as dbrs from seewomq.edu_userlogs bl
where bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')
and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')
and bl.platform='ttmq';
'''
#获取的值为2列,值为各个书本点播人数
SQL4='''select v.videoName, count(DISTINCT bl.userid) as dbrs from seewomq.edu_video v left join seewomq.edu_userlogs bl
on v.id=bl.videoID and bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00') and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')
and bl.platform='ttmq' group by v.videoName order by count(DISTINCT bl.userid) desc;
'''
#定义个方法执行查询sql操作
def get_data(db,sql):
# 使用cursor()方法获取操作游标
cur1 = db.cursor()
try:
cur1.execute(sql) #执行sql语句
return cur1.fetchall() #获取查询的所有记录
except Exception as e:
raise e
finally:
db.close() #关闭连接
print('################################################################')
print('准备获取sql1------data------------------------------------')
#打开数据库连接
db1= pymysql.connect(host=mysql_host,user=mysql_user,
password=mysql_pass,db=mysql_db1,port=mysql_port)
data_1=get_data(db1,SQL1)
#遍历结果
#获取的值为8列个数字，第一个是日期，第2个是新用户访问，第3个是点击人数，第4个是点击次数，5是包月人数，6是包月失败，7老包月，8点播人数
sql_1_data_list=[]
for row in data_1 :
tmp_list=[]
row_date = row[0]
row_new_user = row[1]
row_click_user = row[2]
row_click_count=row[3]
row_order_users=row[4]
row_order_fails=row[5]
row_order_old=row[6]
row_dianbo=row[7]
tmp_list.append(row_date)
tmp_list.append(row_new_user)
tmp_list.append(row_click_user)
tmp_list.append(row_click_count)
tmp_list.append(row_order_users)
tmp_list.append(row_order_fails)
tmp_list.append(row_order_old)
tmp_list.append(row_dianbo)
sql_1_data_list.append(tmp_list)
print('sql----1------data---****************************')
print('准备获取sql2------data------------------------------------')
db1= pymysql.connect(host=mysql_host,user=mysql_user,
password=mysql_pass,db=mysql_db1,port=mysql_port)
data_2=get_data(db1,SQL2)
sql_2_data_list=[]
for row in data_2:
sql_2_data_list.append(row[0])
sql_2_data_list.append(row[1])
print(sql_2_data_list)
print('sql----2------data---****************************')
print('准备获取sql3------data------------------------------------')
db1= pymysql.connect(host=mysql_host,user=mysql_user,
password=mysql_pass,db=mysql_db1,port=mysql_port)
data_3=get_data(db1,SQL3)
sql_3_data_list=[]
for row in data_3:
sql_3_data_list.append(row[0])
print(sql_3_data_list)
print('sql----3------data---****************************')
print('准备获取sql4------data-------------------------------------------')
db1= pymysql.connect(host=mysql_host,user=mysql_user,
password=mysql_pass,db=mysql_db1,port=mysql_port)
data_4=get_data(db1,SQL4)
sql_4_data_list=[]
for row in data_4:
tmp_list=[]
tmp_list.append(row[0])
tmp_list.append(row[1])
sql_4_data_list.append(tmp_list)
#
print(sql_4_data_list)
print('sql----4------data---****************************')
print('#########################准备写excel表###########################################')
# 创建 xls 文件对象
wb = xlwt.Workbook()
# 新增一个表单
sh = wb.add_sheet('Last_month')
#先写标题列
sh.write(0,0,'日期')
sh.write(0,1,'新用户访问')
sh.write(0,2,'点击人数')
sh.write(0,3,'点击次数')
sh.write(0,4,'包月人数')
sh.write(0,5,'失败人数')
sh.write(0,6,'老包月访问')
sh.write(0,7,'点播人数')
sh.write(0,8,'活跃率')
sh.write(0,9,'书本')
sh.write(0,10,'点击人数')
# 按位置添加数据
#先写excel添加---sql—1-data
data_1_lenth=len(sql_1_data_list)
print(sql_1_data_list)
start_row_num=1
for data_list in sql_1_data_list:
start_col_num=0
for item in data_list:
sh.write(start_row_num,start_col_num,item)
start_col_num+=1
start_row_num+=1
#第一列写合计
sh.write(data_1_lenth+1,0,'合计')
#写excel添加---sql—2-data
# sql_2_row_start=31
sql_2_row_start=data_1_lenth+1
sql_2_col_start=2
print(sql_2_data_list)
for sql_2_data in sql_2_data_list:
sh.write(sql_2_row_start,sql_2_col_start,sql_2_data)
sql_2_col_start+=1
#写excel添加---sql—3-data
sql_3_row_start=data_1_lenth+1
sql_3_col=7
sh.write(sql_3_row_start,sql_3_col,sql_3_data_list[0])
#写excel添加---sql—4-data
sql_4_row_start=1
sql_4_item_1_col=9
sql_4_item_2_col=10
for sql_4_data in sql_4_data_list:
sh.write(sql_4_row_start,sql_4_item_1_col,sql_4_data[0])
sh.write(sql_4_row_start,sql_4_item_2_col,sql_4_data[1])
sql_4_row_start+=1
filename=str(ThisMonthToday)
# 保存文件
wb.save(filename+'月报'+'.xls')
