oracle 日期输入,带有日期用户输入的Python中的Oracle SQL查询

from__future__importprint_functionimportpandasaspdimportnumpyasnpfromxlsxwriter.utilityimportxl_rowcol_to_cellimportconfigimportpandas.io.formats.excel

pandas.io.formats.excel.header_style=NoneclassSummary():def__init__(self,config):self.config=configdefconnect_TFR_summary(self):#sql_VDX_summary="select * from (select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", SDATE as "'Start_Date'", EDATE as "'End_Date'", initcap(CREATIVE_DESC) as "'Placement_Name'",COST_TYPE_DESC as "'Cost_type'",UNIT_COST as "'Unit_Cost'",BUDGET as "'Planned_Cost'",BOOKED_QTY as "'Booked_Imp#Booked_Eng'" from TFR_REP.SUMMARY_MV where (IO_ID = {}) AND (DATA_SOURCE = 'KM') AND SDATE >= TO_CHAR(TO_DATE("'{}'", 'YYYY-MM-DD'),'YYYY-MM-DD') AND EDATE <= TO_CHAR(TO_DATE("'{}'",'YYYY-MM-DD'),'YYYY-MM-DD') AND CREATIVE_DESC IN(SELECT DISTINCT CREATIVE_DESC FROM TFR_REP.SUMMARY_MV) ORDER BY PLACEMENT_ID) WHERE Placement_Name Not IN ('Pre-Roll - Desktop','Pre-Roll - Desktop + Mobile','Pre-Roll – Desktop + Mobile','Pre-Roll - In-Stream/Mobile Blend','Pre-Roll - Mobile','Pre-Roll -Desktop','Pre-Roll - In-Stream')".format(# self.config.IO_ID, self.config.SDATE, self.config.EDATE)sql_VDX_summary="select * from (select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", SDATE as "'Start_Date'", EDATE as "'End_Date'", initcap(CREATIVE_DESC) as "'Placement_Name'",COST_TYPE_DESC as "'Cost_type'",UNIT_COST as "'Unit_Cost'",BUDGET as "'Planned_Cost'",BOOKED_QTY as "'Booked_Imp#Booked_Eng'" from TFR_REP.SUMMARY_MV where (IO_ID = {}) AND (DATA_SOURCE = 'KM') AND SDATE >= TO_DATE('{}', 'YYYY-MM-DD') AND EDATE <= TO_DATE('{}','YYYY-MM-DD') AND CREATIVE_DESC IN(SELECT DISTINCT CREATIVE_DESC FROM TFR_REP.SUMMARY_MV) ORDER BY PLACEMENT_ID) WHERE Placement_Name Not IN ('Pre-Roll - Desktop','Pre-Roll - Desktop + Mobile','Pre-Roll – Desktop + Mobile','Pre-Roll - In-Stream/Mobile Blend','Pre-Roll - Mobile','Pre-Roll -Desktop','Pre-Roll - In-Stream')".format(self.config.IO_ID,self.config.SDATE,self.config.EDATE)sql_VDX_MV="select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", sum(IMPRESSIONS) as "'Impression'", sum(ENGAGEMENTS) as "'Eng'", sum(DPE_ENGAGEMENTS) as "'Deep'", sum(CPCV_COUNT) as "'Completions'" from TFR_REP.KEY_METRIC_MV WHERE (IO_ID = {}) AND (DAY_DESC BETWEEN to_char(TO_DATE(TO_CHAR('{}'), 'MM/DD/YYYY'),'YYYY-MM-DD') AND to_char(TO_DATE(TO_CHAR('{}'),'MM/DD/YYYY'),'YYYY-MM-DD')) GROUP BY PLACEMENT_ID, PLACEMENT_DESC ORDER BY PLACEMENT_ID".format(self.config.IO_ID,self.config.SDATE,self.config.EDATE)sql_Display_summary="select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", SDATE as "'Start_Date'", EDATE as "'End_Date'", CREATIVE_DESC as "'Placement_Name'", COST_TYPE_DESC as "'Cost_type'",UNIT_COST as "'Unit_Cost'",BUDGET as "'Planned_Cost'", BOOKED_QTY as "'Booked_Imp#Booked_Eng'" FROM TFR_REP.SUMMARY_MV where (IO_ID = {}) AND SDATE >= TO_DATE('{}', 'YYYY-MM-DD') AND EDATE <= TO_DATE('{}','YYYY-MM-DD') AND DATA_SOURCE = 'SalesFile' ORDER BY PLACEMENT_ID".format(self.config.IO_ID,self.config.SDATE,self.config.EDATE)#sql_Display_MV="select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", sum(VIEWS) as "'Delivered_Impresion'", sum(CLICKS) as "'Clicks'", sum(CONVERSIONS) as "'Conversion'" from TFR_REP.DAILY_SALES_MV WHERE (IO_ID = {}) AND (DAY_DESC BETWEEN to_char(TO_DATE(TO_CHAR('{}'), 'MM/DD/YYYY'),'YYYY-MM-DD') AND to_char(TO_DATE(TO_CHAR('{}'),'MM/DD/YYYY'),'YYYY-MM-DD')) GROUP BY PLACEMENT_ID, PLACEMENT_DESC ORDER BY PLACEMENT_ID".format(#self.config.IO_ID, self.config.SDATE, self.config.EDATE)sql_Display_MV="select * from TFR_REP.DAILY_SALES_MV"sql_preroll_summary="select * from (select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", SDATE as "'Start_Date'", EDATE as "'End_Date'", initcap(CREATIVE_DESC) as "'Placement_Name'",COST_TYPE_DESC as "'Cost_type'",UNIT_COST as "'Unit_Cost'",BUDGET as "'Planned_Cost'",BOOKED_QTY as "'Booked_Imp#Booked_Eng'" from TFR_REP.SUMMARY_MV where (IO_ID = {}) AND SDATE >= TO_DATE('{}', 'YYYY-MM-DD') AND EDATE <= TO_DATE('{}','YYYY-MM-DD') AND (DATA_SOURCE = 'KM') AND CREATIVE_DESC IN(SELECT DISTINCT CREATIVE_DESC FROM TFR_REP.SUMMARY_MV) ORDER BY PLACEMENT_ID) WHERE Placement_Name IN ('Pre-Roll - Desktop','Pre-Roll - Desktop + Mobile','Pre-Roll – Desktop + Mobile','Pre-Roll - In-Stream/Mobile Blend','Pre-Roll - Mobile','Pre-Roll -Desktop','Pre-Roll - In-Stream')".format(self.config.IO_ID,self.config.SDATE,self.config.EDATE)sql_preroll_mv="select substr(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) as "'Placement#'", sum(IMPRESSIONS) as "'Impression'", sum(CPCV_COUNT) as "'Completions'" from TFR_REP.KEY_METRIC_MV WHERE (IO_ID = {}) AND (DAY_DESC BETWEEN to_char(TO_DATE(TO_CHAR('{}'), 'MM/DD/YYYY'),'YYYY-MM-DD') AND to_char(TO_DATE(TO_CHAR('{}'),'YYYY-MM-DD')) GROUP BY PLACEMENT_ID, PLACEMENT_DESC ORDER BY PLACEMENT_ID".format(self.config.IO_ID,self.config.SDATE,self.config.EDATE)returnsql_VDX_summary,sql_Display_summary,sql_preroll_summary,sql_Display_MV,sql_VDX_MV,sql_preroll_mvdefread_query_summary(self):sql_VDX_summary,sql_Display_summary,sql_preroll_summary,sql_Display_MV,sql_VDX_MV,sql_preroll_mv=self.connect_TFR_summary()read_sql_Display_mv=pd.read_sql(sql_Display_MV,self.config.conn)