how to compare datetime using psycopg2 in python3?

Question

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.

Adrian Klaver · Accepted Answer · 2022-09-08 15:55:51Z

0

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()])

answered Sep 8, 2022 at 15:55

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to compare datetime using psycopg2 in python3?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related