how to compare datetime using psycopg2 in python3?

user956424 0
Viewed 29

I wish to execute the statement to delete records from a postgresql table older than 45 days in my python script as below:

Consider only the code below:

import psycopg2
from datetime import datetime

cur = conn.cursor()
mpath = None
sql1 = cur.execute(
    "Delete from table1 where mdatetime < datetime.today() - interval '45 days'")

This causes the following error:

psycopg2.errors.InvalidSchemaName: schema "datetime" does not exist LINE 1: Delete from logsearch_maillogs2 where mdatetime < datetime.t...

How do I exactly change the format or resolve this. Do I need to convert. Saw a few posts which say that postgresql DateTime doesn't exist in PostgreSQL etc, but didn't find exact code to resolve this issue. Please guide.

postgresql-11 psycopg2 python-3.x
1 Answers

The query is running in Postgres not Python you need to use SQL timestamp function not Python ones if you are writing a hard coded string. So datetime.today() --> now() per Current Date/time.

sql1 = cur.execute(
    "Delete from table1 where mdatetime < now() - interval '45 days'")

Or you need to use parameters per here Parameters to pass in a Python datetime if you want a dynamic query.


sql1 = cur.execute(
    "Delete from table1 where mdatetime < %s - interval '45 days'", [datetime.today()])
Adrian Klaver0
Related