Skip to main content
 首页 » 编程设计

python之带有日期用户输入的 Python 中的 Oracle SQL 查询

2025年01月19日8zengkefu

我试图在 python 中运行 SQL 查询,但我收到错误 not a valid month while passing dates using user input。

该表有一个格式为“MM/DD/YYYY”的日期列。我将日期传递为“YYYY-MM-DD”

这是查询。

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) 

这是python代码: 代码太大,所以我只放一部分阅读SQL。

from __future__ import print_function 
import pandas as pd 
import numpy as np 
from xlsxwriter.utility import xl_rowcol_to_cell 
import config 
import pandas.io.formats.excel 
pandas.io.formats.excel.header_style = None 
 
class Summary(): 
    def __init__(self,config): 
        self.config=config 
 
    def connect_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) 
 
        return sql_VDX_summary,sql_Display_summary,sql_preroll_summary,sql_Display_MV,sql_VDX_MV,sql_preroll_mv 
 
    def read_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) 

请您参考如下方法:

假设您以 "YYYY-MM-DD" 格式传递日期并且 DAY_DESC 是日期类型,您可以使用日期文字,这是 ANSI 标准并避免TO_DATE

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 DATE '{}' 
        AND DATE '{}' 
GROUP BY PLACEMENT_ID 
    ,PLACEMENT_DESC 
ORDER BY PLACEMENT_ID; 

The table has a date column in "MM/DD/YYYY" format

Date 数据类型在数据库中没有任何格式。只是显示会根据您的 session 参数而有所不同。但是,如果您的意思是 DAY_DESC 是字符串 (CHAR/VARCHAR2) 类型,则可以将其转换为:-

TO_DATE(DAY_DESC,'MM/DD/YYYY') BETWEEN DATE '{}' AND DATE '{}' 

请避免在数据库中将日期存储为字符串。