import psycopg2
import xlwt
import datetime
#
'''
思路:
1.获取数据库连接
2.创建excel并初始化
3.写入excel
4.保存到本地
'''
#一、获取数据库连接
conn = psycopg2.connect(host="10.107.22.6", port=5432,
user="****", password="***",
database= '****'#
)
# print("数据库连接成功")
#获取游标
curs=conn.cursor()
#SQL语句
#sql = """ select hour,countdate,counttime from d_mall_hour_count_data where countdate >= date '2023-01-01' and countdate < date '2023-02-01' """
sql= """ select hour,countdate,counttime,(select sum(innum)-sum(outnum) from d_mall_hour_count_data t2 where t2.countdate=t1.countdate and t2.hour <=t1.hour) as zhiliu from d_mall_hour_count_data t1 where countdate >=date '2022-07-01' and countdate< date '2022-08-01' group by counttime,countdate,hour order by countdate """
#sql = """select "hour",countdate,counttime,(select sum(innum)-sum(outnum) from d_mall_hour_count_data t2 where t2.countdate=t1.countdate and t2."hour"<=t1."hour") as zhiliu from d_mall_hour_count_data t1 where counttime>=date '2022-07-01' and counttime < date '2022-08-01' group by counttime,countdate,"hour" order by counttime """
#curs.mogrify(sql)
curs.execute(sql)
rows=curs.fetchall()#获取所有数据
print(rows)
#二、初始化excel文件
w=xlwt.Workbook(encoding='utf-8')
style=xlwt.XFStyle()#初始化样式
font=xlwt.Font()#创建字体样式
font.name="微软雅黑"
style.font=font#把字体添加到样式中
ws=w.add_sheet("用户信息",cell_overwrite_ok=True)
#三、把数据写入excel
'''创建excel的列名'''
title="hour,date,datetime,zhiliu"
title=title.split(",")
'''#使用循环写入数据'''
for i in range(len(title)):
ws.write(0,i,title[i],style)
#开始写入数据
for i in range(len(rows)):
row=rows[i]
for j in range(len(row)):
if row[j]:
item=row[j]
ws.write(i+1,j,item,style)
#四、数据保存到本地
path='shuju12.xls'
w.save(path)
conn.close()
print("保存成功!")
通过python 连接数据库查询数据,生成表格
最新推荐文章于 2024-06-05 22:32:42 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)