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!