postgres: select and return results before dropping table

I have the following sql, where i want to return the selected data

insert into test(id,name) select * from tmp_tbl on conflict(id) do update set name = EXCLUDED.name;
with selected_data as (select * from tmp_tbl) select * from selected_data;
drop table if exists tmp_tbl;

THis is the python code:

with psycopg.connect(self.connect_str, autocommit=True) as conn:
    with conn.cursor() as cur:
        cur.execute(sql_full)
        rows = cur.fetchall()
        colnames = [desc[0] for desc in cur.description]
        df_result = pd.DataFrame(rows, columns=colnames)                                       
        return df_result

This produces error:

 File "/usr/local/lib/python3.12/site-packages/psycopg/cursor.py", line 223, in fetchall
    self._check_result_for_fetch()
  File "/usr/local/lib/python3.12/site-packages/psycopg/_cursor_base.py", line 588, in _check_result_for_fetch
    raise e.ProgrammingError("the last operation didn't produce a result")
psycopg.ProgrammingError: the last operation didn't produce a result

I can execute that sql directly in sql client(dbweaver) and it works. but the python/psycopg code does not. any help appreciated

You need to sign in to view this answers

Related Post