psycopg2 SELECT query with inbuilt functions

Question

I have the following SQL statement where i am reading the database to get the records for 1 day. Here is what i tried in pgAdmin console -

SELECT * FROM public.orders WHERE createdat >= now()::date AND type='t_order'

I want to convert this to the syntax of psycopg2but somehow it throws me errors -

Database connection failed due to invalid input syntax for type timestamp: "now()::date"

Here is what i am doing -

query = f"SELECT * FROM {table} WHERE (createdat>=%s AND type=%s)"
cur.execute(query, ("now()::date", "t_order"))
records = cur.fetchall()

Any help is deeply appreciated.

Answer 1

1. DO NOT use f strings. Use proper Parameter Passing

2. now()::date is better expressed as current_date. See Current Date/Time.

3. You want:

query = "SELECT * FROM public.orders WHERE (createdat>=current_date AND type=%s)"
cur.execute(query, ["t_order"])

If you want dynamic identifiers, table/column names then:

from psycopg2 import sql

query = sql.SQL("SELECT * FROM {} WHERE (createdat>=current_date AND type=%s)").format(sql.Identifier(table))

cur.execute(query, ["t_order"])

For more information see sql.