problem with fetchall when inserting multiple rows using sqlalchemy

Question

Good Afternoon, I am using psycopg and sqlalchemy to make an insert into and returning query. I have successfully inserted and returned, but in a roundabout way. I am inserting the data and returning the values in a temporary table and then selecting the values in the temporary table, as shown below.

from sqlalchemy.engine import create_engine
from sqlalchemy import text
from sqlalchemy import bindparam
import psycopg

def makePgEngine():
        global pgEngine
        pgEngine = create_engine("postgresql+psycopg://postgres:postgres@localhost:5412/postgres")

def insert_into_returning():
        makePgEngine()
        sql_text = "with r as (insert into public.numeric_data_source (x_field,y_field,z_field)values(:x_field,:y_field,:z_field) returning x_field, y_field, z_field) insert into temp_numeric_data_source (x_field, y_field, z_field)select x_field, y_field, z_field from r"
        qry =text(sql_text).bindparams(
            bindparam('x_field'),
            bindparam('y_field'),
            bindparam('z_field')
                )
        params=({'x_field':-99,'y_field':-98,'z_field':-97},
                            {'x_field':-98,'y_field':-97,'z_field':-96},
                            {'x_field':-97,'y_field':-96,'z_field':-95})
        with pgEngine.connect() as con:
            con.execute(text("truncate table public.numeric_data_source"))
            con.execute(text("create temp table temp_numeric_data_source as select x_field, y_field, z_field from public.numeric_data_source"))
            con.execute(qry, params)
            data=con.execute(text("select * from temp_numeric_data_source"))
            print(data.fetchall())
            con.commit()

Is there a way I can get around the use of the temporary table? I would like to retain the use of the text object in sqlalchemy. Thank you for your assistance.

Answer 1

I think it is probably easiest to just specify the table you are working with rather than trying to force text() to generate an appropriate query. Something like this:

# table and column are more basic versions of Table and Column
from sqlalchemy import table, insert, column

def populate(conn):
    values = [
        {'x_field':-99,'y_field':-98,'z_field':-97},
        {'x_field':-98,'y_field':-97,'z_field':-96},
        {'x_field':-97,'y_field':-96,'z_field':-95}
    ]
    numeric_data_source_t2 = table("numeric_data_source",
        column("id"),
        column("x_field"),
        column("y_field"),
        column("z_field"))
    result = conn.execute(insert(numeric_data_source_t2).returning(numeric_data_source_t2), values)

    for row in result:
        print (row)

    conn.commit()

Here is a more complicated version using Table.

from sqlalchemy import MetaData, Table, Integer, Column, Float
from sqlalchemy.sql import insert

# Tables that need to work together need to be in the same metadata but we just use one here.
metadata = MetaData()

# This only minimally defines a table for the purpose of generating an insert.
numeric_data_source_t = Table("numeric_data_source", metadata,
    Column("id", Integer, primary_key=True),
    Column("x_field", Float),
    Column("y_field", Float),
    Column("z_field", Float))

def insert_into_returning(conn):
    values = [
        {'x_field':-99,'y_field':-98,'z_field':-97},
        {'x_field':-98,'y_field':-97,'z_field':-96},
        {'x_field':-97,'y_field':-96,'z_field':-95}
    ]
    # Insert the data and return it back along with its id.
    result = conn.execute(insert(numeric_data_source_t).returning(numeric_data_source_t), values)
    for row in result:
        print(row)
    conn.commit()

Maybe someone can chime in on how to force text() insert an insert() to then specify returning() with columns but I could not find a way.