How to raw query with datetime in SqlAlchemy Python3-CodePudding

So I am trying to query from a table messages on the basis of a datetime field timeStamp but I keep getting an error. Maybe the way I am trying to query is not correct.

def get_test(self, start_date, end_date):
        
        query = 'SELECT * from messages WHERE messages."timeStamp" > {start_date} and messages."timeStamp" < {end_date};'
        res = self.session.execute(query)
        self.session.commit()
        
        return res.fetchall()

When I run this code, I get the following error.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "00"
LINE 1: ... messages WHERE messages."timeStamp" > 2021-06-01 00:00:00 a...

I also tried using datetime.strptime method but that didn't work too.

def get_test(self, start_date, end_date):
        
        query = f'''SELECT * from messages WHERE messages."timeStamp" > {datetime.strptime(start_date, "%Y-%m-%d %H:%M:%S")}   
        and messages."timeStamp" < {datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S")};'''
        res = self.session.execute(query)
        self.session.commit()
        
        return res.fetchall()

I also tried using the query parameters but that didn't work either.

 def get_test(self, start_date, end_date):
        
        query = f'SELECT * from messages WHERE messages."timeStamp" > %s and messages."timeStamp" < %s;'
        res = self.session.execute(query, (datetime.strptime(start_date, "%Y-%m-%d %H:%M:%S"), 
                                           datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S")))
        self.session.commit()
        
        return res.fetchall()

Would really appreciate if anyone could help me out here.

CodePudding user response：

I think there are a couple of things going on here.

I don't think your f-strings are populating and strptime is for turning strings into dates, not the other way round.

I'd write it like this:

query = f'SELECT * from messages WHERE messages."timeStamp" > {start_date.isoformat()} and messages."timeStamp" < {end_date.isoformat()};'