CODEWITHSUNDEEP
sqlpandas
SJJ
SJJ

Reputation: 25

Pandas read_sql columns not working. How do I put self.* in query

I'm using pandas.read_sql() command to get data from my AWS database. I checked the AWS database was working well. But self.start_date, self.last_date, self.serial_no not identify in the query(strQry).

Can anyone help me?

class Information:
    def __init__(self, start_date, last_date, serial_no):
        self.start_date = start_date
        self.last_date = last_date
        self.serial_no = serial_no

    def date_info(self):
        strQry = f'''select 
                    distinct executetime_dt
                from
                    ads.ads_ep_info_final
                where
                    jamcustomer_id = {self.serial_no} and 
                    (executetime_dt >= {self.start_date} and executetime_dt <= {self.last_date})
                order by 
                    executetime_dt ASC
                limit 20'''

        df_date = pd.read_sql(strQry, con=conn)
        return df_date

start_date = '2022-01-01 00:00:00'
last_date = '2022-05-16 00:00:00'
serial_no = '220209'

data = Information(start_date, last_date, serial_no)
data.date_info()

[Error]

   1699         raise e
-> 1701 self.handle_messages(cursor)
...
                    jamcustomer_id = 220209 and 
                    (executetime_dt >= 2022-01-01 00:00:00 and executetime_dt <= 2022-05-16 00:00:00)
                order by 
                    executetime_dt ASC
                limit 20': {'S': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "00"', 'P': '237', 'F': '/home/ec2-user/padb/src/pg/src/backend/parser/parser_scan.l', 'L': '714', 'R': 'yyerror'}

Upvotes: 0

Views: 231

Answers (1)

nbk
nbk

Reputation: 49375

You can use sigle quotes arounfd the dates, or you use parameters

like:

def date_info(self):
    strQry = f'''select 
                distinct executetime_dt
            from
                ads.ads_ep_info_final
            where
                jamcustomer_id = ? and 
                (executetime_dt >= ? and executetime_dt <= ?)
            order by 
                executetime_dt ASC
            limit 20'''

    df_date = pd.read_sql(strQry, con=conn,params=(self.serial_no, self.start_date, self.last_date))
    return df_date

Upvotes: 1

Related Questions