CODEWITHSUNDEEP
pythonsqloracle-databasepandas
abhilash mishra
abhilash mishra

Reputation: 255

Oracle SQL Query In Python with date User Input

I'm trying to run SQL Query in python but I'm getting Error not a valid month while passing dates using user input.

The table has a date column in "MM/DD/YYYY" format. I'm passing date as "YYYY-MM-DD"

Here is the Query.

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)

Here is the python Code: the code is too big, so I'm just putting a part of reading 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)

Upvotes: 2

Views: 1246

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Assuming that you are passing dates in "YYYY-MM-DD" format and DAY_DESC is date type, you could use date literals, which are ANSI standard and avoid 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 datatypes don't have any formats in database. It is just the display that varies according to you session parameters. However, If you mean DAY_DESC is of string (CHAR/VARCHAR2)type, you could use it after converting it as:- 

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

Please avoid storing dates as character strings in databases.

Upvotes: 1

Related Questions