Is there a way to use temporary tables with arcpy query layer functions?

[JesseTemplin2]

Hello,

I would like to create feature layers from data in temporary tables. I have tried the following:

database_connection = r"C:\project\database.sde"
create = "CREATE TEMP TABLE temp_example(object_id int, name varchar(80))"
insert = "INSERT INTO temp_example VALUES (1, 'wtf')"
select_temp = "SELECT * FROM temp_example"
select_real = "SELECT report_id, report_type FROM report LIMIT 1"

arc_sde = arcpy.ArcSDESQLExecute(database_connection)
arc_sde.execute(create)
arc_sde.execute(insert)
result = arc_sde.execute(select_temp)
for row in result:
    print(row)

# this works
table = arcpy.MakeQueryLayer_management(
    input_database      = database_connection,
    out_layer_name      = "Real_Table",
    query               = select_real,
    oid_fields          = "report_id"
)[0]

# this fails
table = arcpy.MakeQueryLayer_management(
    input_database      = database_connection,
    out_layer_name      = "Temp_Table",
    query               = select_temp,
    oid_fields          = "object_id"
)[0]

This returns the classic Error 999999. I suspect ArcSDESQLExecute is creating a separate connection, meaning the tables don't exist in the original database_connection. I have tried passing arc_sde to MakeQueryLayer as the database but then I get the following error:

RuntimeError: ArcSDESQLExecute: Get attribute __class__ does not exist

I have also tried using psycopg2 to create the temporary tables, but still get Error 999999 when trying to use them with arcpy.

Is there another way to use temporary tables with MakeQueryLayer and other arcpy functions?

I am using ArcGIS Pro 2.7 with the Basic license, and the database is PostgreSQL 12.2 with PostGIS 3.01.

Thanks!

[simoxu]

I feel it's something to do the OBJECTID

Can you try this:

create = "CREATE TEMP TABLE temp_example(OBJECTID Long, name varchar(80))"

I have no experience with PostgreSQL and PostGIS, so it's a long shot😀

[JesseTemplin2]

Long isn't a valid Postgres type, and if I use "OBJECTID int" I get the same 999999 error.

Thanks for the suggestion though.

[JoshuaBixby]

Just for kicks, try:

select_temp = "SELECT object_id, name FROM temp_example"

[JesseTemplin2 · Accepted solution]

Same result, but thanks for the suggestion. After looking at the database connections while running these functions, it's clear that ArcSDESQLExecute is creating a separate connection, which explains why the temporary tables aren't visible to MakeQueryLayer.