import cx_Oracle import pandas as pd import openpyxl #连接数据库 conn = cx_Oracle.connect('資料數據庫') sql="SELECT * FROM (SELECT * FROM ( SELECT NVL(E.locdesc,'') LOCATION,D.LINE,COUNT(*) QTY FROM REPAIR@CQABNB02 A,MO_D B,ISN C,MO_RUTE D,location E WHERE E.CODE=A.PLACE AND A.ISN=B.ISN AND B.ISN=C.ISN AND C.ISN=D.ISN AND D.SQ1=1 AND D.LIE IN('BN01B','BN02','BN09','BN10','BN11','BN12','BN13''BN14''BN15''BN16') AND A.RTIME BETWEEN TO_DATE('20210910200000','YYYYMMDDHH24MISS')AND TO_DATE('20210911070059','YYYYMMDDHH24MISS') GROUP BY ROLLUP(A.PLACE,D.LINE,E.locdesc)) PIVOT (SUM(QTY) FOR LINE IN ('BN01B','BN02','BN09','BN10','BN11','BN12','BN13''BN14''BN15''BN16'))) ORDER BY LOCATION" data=pd.read_sql(sql,conn) writer=pd.ExcelWriter('20210910.xlsx') #filename为导excel的路径 data.to_excel(writer,'sheet',index=False) writer.save() conn.close()
时间: 2023-09-13 19:09:19 浏览: 90
pandas实现to_sql将DataFrame保存到数据库中
这是一个Python脚本,用于连接Oracle数据库,查询指定的数据,并将查询结果导出到Excel文件中。脚本中使用了cx_Oracle、pandas和openpyxl等Python库。其中,cx_Oracle库用于连接Oracle数据库,pandas库用于处理数据,openpyxl库用于导出Excel文件。你提供的脚本中查询的是某个时间段内,指定生产线和位置的维修数量统计结果,并按位置进行了排序。
阅读全文