Joining tables across databases through Python

Viewed 39

I have 2 tables in 2 different databases, both Postgres. I'm getting some records from one of the tables in Python and I'd like to join this with the other table on Postgres (on a diffferent DB). I have written the following query to do this:

sql = """
SELECT
    *,
    extra::json ->> 'field_name' AS field_name
FROM
    tablename
WHERE
    sent_at >= %(notification_date)s
    and sent_at <= %(notification_date)s + interval '1 hour' 
    and to_user_id = %(user_id)s
"""
df_results = db_query('DB_Name', sql,
                    params={'notification_date': tuple(df['sent_at']),
                            'user_id': tuple(df['user_id'])})

But this gives me the following error:

UndefinedFunction: operator does not exist: timestamp without time zone >= record
LINE 8:     sent_at >= ('2022-03-02T06:49:06.416170'::timestamp, '20...

It's looking like notification_date appears as a list within brackets. How do I make this a row-wise operation just like joining tables in SQL? I'd like to pass only one value of notification_date and only one value of user_id at a time for a row-wise comparison.

I don't want to put this in a loop where I pass one value at a time. The table I am dealing with is massive and it'd take way too much time for the code to finish. Even then, I suspect it might just time out and not execute at all.

Any help would be greatly appreciated.

0 Answers
Related