Python查询PostgreSQL并将数据写入excel的总结案例

最新推荐文章于 2024-03-31 09:02:29 发布

leo_qiu_s

最新推荐文章于 2024-03-31 09:02:29 发布

阅读量1.5k

点赞数 1

本文链接：https://blog.csdn.net/qiudechao1/article/details/89152686

版权

全部代码如下，没时间写讲解。

import psycopg2 #PostgreSQL查询包
import xlwt #写入excel的工具包
import datetime #时间函数包
import time

def conn():
    conn = psycopg2.connect(
        host='119.3.5.255',
        port='5432',
        database='ay_test',
        user='aoyang',
        password='Aoyang123456',
    )
    return conn

#判断是否是一个有效的日期字符串
def is_valid_date(strdate):
    try:
        if ":" in strdate:
            time.strptime(strdate, "%Y-%m-%d %H:%M:%S")
        return True
    except:
        return False
#判断结束时间是否大于开始时间，并循环录入时间，直到获取正确时间格式
def dateCheck():
    while True:
        day1 = input('输入查询开始时间，格式2019-03-02 07:30:00 ')
        day2 = input('输入查询结束时间，格式2019-03-03 07:30:00 ')
        if is_valid_date(day1) and is_valid_date(day2):
            startTime = datetime.datetime.strptime(day1, "%Y-%m-%d %H:%M:%S")
            endTime = datetime.datetime.strptime(day2, "%Y-%m-%d %H:%M:%S")
            if startTime < endTime:
                return day1,day2
            else:
                print("结束日期必须大于开始日期，请重新输入")
        else:
            print("输入日期有误，请重新输入")


def query1(conn,day1,day2):
    cursor =conn.cursor()
    sql_1='''select count(value) from "D_COLLECT_MANUALDATA" 
    where collect_id like  'ST%' 
    and collect_time >= '{0}' ::TIMESTAMP and collect_time <= '{1}' ::TIMESTAMP ;    
    '''.format(day1,day2)

    sql_2='''select count(value) from "D_COLLECT_MANUALDATA" 
    where collect_id like  'SP%' 
    and collect_time >= '{0}' ::TIMESTAMP and collect_time <= '{1}' ::TIMESTAMP ;    
    '''.format(day1,day2)

    sql_3='''select count(value) from "D_COLLECT_MANUALDATA" 
    where collect_id like  'AC%' 
    and collect_time >= '{0}' ::TIMESTAMP and collect_time <= '{1}' ::TIMESTAMP ;    
    '''.format(day1,day2)

    cursor.execute(sql_1)
    result1=cursor.fetchall()
    cursor.execute(sql_2)
    result2=cursor.fetchall()
    cursor.execute(sql_3)
    result3=cursor.fetchall()
    cursor.close()
    return (result1[0][0],result2[0][0],result3[0][0])

def query2(conn,day0,day1,day2):
    cursor = conn.cursor()
    #原材料领用
    sql_1 = 'select count(*) from "D_MAT_REQU" where requ_time >= %s and requ_time <= %s;'
    sql_2 = 'select count(*) from "D_MAT_REQU" where requ_time <= %s;'
    cursor.execute(sql_1,(day0,day1))
    result1 = cursor.fetchall() #原材料领用当日
    cursor.execute(sql_2, (day1,))
    result2 = cursor.fetchall() #原材料领用累计
    #原材料质检
    sql_3 = 'select count(*) from "D_QLT_MATERIALINFO" where create_time >= %s and create_time <= %s;'
    sql_4 = 'select count(*) from "D_QLT_MATERIALINFO" where create_time <= %s ::TIMESTAMP;'
    cursor.execute(sql_3,(day1,day2)) #原材料质检当日
    result3=cursor.fetchall()
    cursor.execute(sql_4,(day2,))
    result4 = cursor.fetchall() #原材料质检累计
    #半成品质检
    sql_5='select count(*) from "D_QLT_SIMPPRODUCT" where samp_time >= %s::TIMESTAMP and samp_time <= %s::TIMESTAMP;'
    sql_6='select count(*) from "D_QLT_SIMPPRODUCT" where samp_time <= %s::TIMESTAMP;'
    cursor.execute(sql_5,(day1,day2))
    result5=cursor.fetchall() #半成品质检当日
    cursor.execute(sql_6,(day2,))
    result6 = cursor.fetchall() #半成品质检累计
    #成品质检
    sql_7 = 'select count(*) from "D_QLT_PRODUCT" where inspec_time >=%s and inspec_time <=%s;'
    sql_8 = 'select count(*) from "D_QLT_PRODUCT" where inspec_time <=%s;'
    cursor.execute(sql_7,(day1,day2))
    result7=cursor.fetchall() #成品质检当日
    cursor.execute(sql_8,(day2,))
    result8=cursor.fetchall() #成品质检累计
    sql_9 = 'select count(*) from "D_AC_PACKING" where create_time >=%s and create_time <=%s;'
    sql_10 = 'select count(*) from "D_AC_PACKING" where create_time <= %s;'
    cursor.execute(sql_9,(day1,day2))
    result9 = cursor.fetchall()
    cursor.execute(sql_10,(day2,))
    result10 = cursor.fetchall()
    #成品打包记录
    sql_11 = 'select  count(*) from "D_PKG_PACKING" where prod_time >=%s::TIMESTAMP and prod_time <=%s::TIMESTAMP;'
    sql_12 = 'select  count(*) from "D_PKG_PACKING" where prod_time <=%s::TIMESTAMP;'
    cursor.execute(sql_11,(day1,day2))
    result11=cursor.fetchall() #成品打包当日
    cursor.execute(sql_12,(day2,))
    result12 = cursor.fetchall() #成品打包累计

    #浆粕配比
    sql_13 = 'select count(*) from "F_PUL_CHANGE" where create_time >= %s::TIMESTAMP and create_time <=%s::TIMESTAMP;'
    sql_14='select count(*) from "F_PUL_CHANGE" where create_time <=%s;'
    cursor.execute(sql_13,(day1,day2))
    result13 =cursor.fetchall() #浆粕配比当日
    cursor.execute(sql_14,(day2,))
    result14=cursor.fetchall() #浆粕配比累计
    #工艺变更
    sql_15 = 'select count(*) from "D_CRAFT_CHANGE" where create_time >= %s::TIMESTAMP and create_time <= %s::TIMESTAMP;'
    sql_16 = 'select count(*) from "D_CRAFT_CHANGE" where  create_time <= %s::TIMESTAMP;'
    cursor.execute(sql_15,(day1,day2))
    result15 = cursor.fetchall()
    cursor.execute(sql_16,(day2,))
    result16 = cursor.fetchall()
    cursor.close()
    return (result1[0][0],result2[0][0],result3[0][0],result4[0][0],result5[0][0],result6[0][0],result7[0][0],result8[0][0],result9[0][0],result10[0][0],result11[0][0],result12[0][0],result13[0][0],result14[0][0],result15[0][0],result16[0][0])

def write_xls(result1,result2):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
    sheet.write(0,1,'实际采集')
    #调节表格宽度
    sheet.col(0).width=180*20
    sheet.col(1).width = 130 * 20
    sheet.col(2).width = 130 * 20
    items1 = ('二原','二纺','二酸')
    for row in range(len(items1)):
        sheet.write(row+1,0,items1[row])

    for row in range(3):
        sheet.write(row+1,1,result1[row])

    sheet.write(4, 1, '当日')
    sheet.write(4, 2, '累计')
    items2 = ('原材料领用', '原材料质检', '半成品质检', '成品质检', '元明粉产量', '成品打包数据', '浆粕配比', '工艺变更')
    for row in range(len(items2)):
        sheet.write(row + 5, 0, items2[row])

    for row in range(8):
        for col in range(2):
            sheet.write(row+5,col+1,result2[row*2+col])
    workbook.save(r'D:\DailyPaper.xls')

def write_collect_count(conn,day1,day2):
    cursor = conn.cursor()
    SQL_eryuan = '''select b.meas_point_name, a.collect_id, count(a.value)  
    from "D_COLLECT_MANUALDATA" a left join "D_EQP_COLLECT" b on a.collect_id = b.collect_id 
    where a.collect_id like  'ST%' 
    and a.collect_time >= '{}'::TIMESTAMP and a.collect_time <= '{}'::TIMESTAMP 
    and b.qr_code_id is not null group by a.collect_id,b.meas_point_name;
    '''.format(day1,day2)
    cursor.execute(SQL_eryuan)
    eryuan=cursor.fetchall()

    SQL_erfang = '''select b.meas_point_name, a.collect_id, count(a.value)  
    from "D_COLLECT_MANUALDATA" a left join "D_EQP_COLLECT" b on a.collect_id = b.collect_id 
    where a.collect_id like  'SP%' 
    and a.collect_time >= '{}'::TIMESTAMP and a.collect_time <= '{}'::TIMESTAMP 
    and b.qr_code_id is not null group by a.collect_id,b.meas_point_name;
    '''.format(day1,day2)
    cursor.execute(SQL_erfang)
    erfang = cursor.fetchall()

    SQL_ersuan = '''select b.meas_point_name, a.collect_id, count(a.value)  
    from "D_COLLECT_MANUALDATA" a left join "D_EQP_COLLECT" b on a.collect_id = b.collect_id 
    where a.collect_id like  'AC%' 
    and a.collect_time >= '{}'::TIMESTAMP and a.collect_time <= '{}'::TIMESTAMP 
    and b.qr_code_id is not null group by a.collect_id,b.meas_point_name;
    '''.format(day1,day2)
    cursor.execute(SQL_ersuan)
    ersuan = cursor.fetchall()
    cursor.close()

    Myexcel = xlwt.Workbook('encoding=utf-8')
    # 查询二原数据采集量
    sheet1 = Myexcel.add_sheet('二原统计',cell_overwrite_ok=True)
    sheet1.col(0).width = 350*20
    sheet1.col(1).width = 150 * 20
    sheet1.col(2).width = 150 * 20
    # 第一行写入项目名称
    items = ('采集点名称', '采集点编号', '采集量')
    for col in range(len(items)):
        sheet1.write(0,col,items[col])

    #循环写入方法一：
    i=0
    for item in eryuan:
        sheet1.write(i + 1, 0, item[0])
        sheet1.write(i + 1, 1, item[1])
        sheet1.write(i + 1, 2, item[2])
        i+=1
    #查询二纺数据采集量
    sheet2 = Myexcel.add_sheet('二纺统计', cell_overwrite_ok=True)
    sheet2.col(0).width = 350*20
    sheet2.col(1).width = 150 * 20
    sheet2.col(2).width = 150 * 20
    #第一行写入项目名称
    for col in range(len(items)):
        sheet2.write(0, col, items[col])
    #循环写入方法二，注意查询结果是tumple套tumple的格式，((),(),())
    for row in range(len(erfang)): # len(erfang) 多少条查询结果
        for col in range(len(erfang[0])): # len(erfang[0]) 第一条查询结果有多少个字段
            sheet2.write(row+1, col, erfang[row][col])

    #查询二酸数据采集量
    sheet3 = Myexcel.add_sheet('二酸统计', cell_overwrite_ok=True)
    sheet3.col(0).width = 350*20
    sheet3.col(1).width = 150 * 20
    sheet3.col(2).width = 150 * 20
    #第一行写入项目名称
    for col in range(len(items)):
        sheet3.write(0, col, items[col])

    #循环写入方法二，注意查询结果是tumple套tumple的格式，((),(),())
    for row in range(len(ersuan)): # len(erfang) 多少条查询结果
        for col in range(len(ersuan[0])): # len(erfang[0]) 第一条查询结果有多少个字段
            sheet3.write(row+1, col, ersuan[row][col])

    Myexcel.save('D:\数据采集量统计.xls')

if __name__ =='__main__':
    #循环录入时间并判断，直到获得正确的时间格式。
    time_str1,time_str2 = dateCheck()
    #原材料领用只能今天录入昨天的，所以需要获取开始时间的前一天
    d1 = datetime.datetime.strptime(time_str1, '%Y-%m-%d %H:%M:%S')
    delta = datetime.timedelta(days=1)
    d0 = d1 - delta
    time_str0 = d0.strftime('%Y-%m-%d %H:%M:%S')

    conn = conn()
    result1 = query1(conn,time_str1,time_str2)
    #开始查询时间的前一天作为原材料领用的查询参数
    result2 = query2(conn,time_str0,time_str1,time_str2)

    write_collect_count(conn, time_str1, time_str2)
    write_xls(result1,result2)
    conn.close()

运行结果导出的两个excel：

第一个excel叫《DailyPaper》，显示总体数据

第二个excel叫《数据采集量统计》，分别有三个sheet，显示每个车间的数据采集细节：

最后在py程序所在的文件夹打开cmd，输入pyinstaller -F wxy_tool.py，打包为一个.exe程序

leo_qiu_s

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
Python查询PostgreSQL并将数据写入excel的总结案例

全部代码如下，没时间写讲解。import psycopg2 #PostgreSQL查询包import xlwt #写入excel的工具包import datetime #时间函数包import timedef conn(): conn = psycopg2.connect( host='119.3.5.255', port='5432',...
复制链接

扫一扫