[Python] 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

Continue reading...