0

I'm trying to pass a condition to a query and I'm using PostgreSQLHook in apache airflow. When I run the dag I'm getting an error

TypeError: Object of type 'datetime' is not JSON serializable

Currently I have my sql stored in a different file and I'm importing the file for execution in my python file.

sql pg_query file

SELECT
    *
FROM
    public.airtimes
WHERE created_at > '{}';

python script

def read_latest_data_from_pg(**kwargs):
    with open('dags/scripts/sql_scripts/pg_query.sql','r') as sqlfile:
            pg_export_data_query=str(sqlfile.read())
    pg_date = '2021-05-01'
    pg_hook = PostgresHook(postgres_conn_id='pg_conn', delegate_to=None, use_legacy_sql=False)
    conn = pg_hook.get_conn()
    cursor = conn.cursor()
    cursor.execute(pg_export_data_query.format(pg_date))
    result = cursor.fetchall()
    print('result', result)
    return result

What I'm I missing?

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Shadow Walker
  • 979
  • 5
  • 27
  • 51
  • It sounds like one of the columns in the table is a datetime type so the db connector casts it to a Python `datetime` in the result. Python's JSON module can't serialise `datetime`s so you need to to cast it to a string representation either in the query or by post-processing the result. – snakecharmerb Sep 16 '21 at 11:17
  • Probably not related, don't use string formatting to interpolate query variables, see https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python?r=SearchResults&s=1|31.9251 – snakecharmerb Sep 16 '21 at 11:18

0 Answers0