How to create a temporary table by passing in data to psycopg2?

Question

I have a pandas dataframe that I want to pass into a psycopg2 execute statement as a temporary table. This should be very simple:

pseudo-code...

string = """
  with temporary_table (id, value) as (values %s)

  select * from temporary_table
"""
cur.execute(string, df)

Where df is just a dataframe with an id and value column.

What would be the syntax to use such that I'd be able to pass this data in as a temporary table and use it in my query?

Answer 1

A test case that I think does what you want:

import psycopg2
from psycopg2.extras import execute_values

con = psycopg2.connect("dbname=test user=postgres host=localhost port=5432")

sql_str = """WITH temporary_table (
    id,
    value
) AS (
    VALUES %s
)
SELECT
    *
FROM
    temporary_table
"""
cur = con.cursor()
execute_values(cur, sql_str, ((1, 2), (2,3)))
cur.fetchall()                                                                                                                                                                                                          
[(1, 2), (2, 3)]

Using execute_values from Fast Execution Helpers.

Answer 2

I would create a temporary table in Postgres database with df.to_sql or execute insert sql query with values, query it and at the end of process delete it