topic Is there a way to use temporary tables with arcpy query layer functions? in ArcGIS API for Python Questions

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

JesseTemplin2 — Thu, 14 Jan 2021 02:10:10 GMT

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!

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

simoxu — Thu, 14 Jan 2021 06:36:13 GMT

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😀

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

JesseTemplin2 — Thu, 14 Jan 2021 19:24:10 GMT

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

Thanks for the suggestion though.

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

JoshuaBixby — Thu, 14 Jan 2021 20:27:40 GMT

Just for kicks, try:

select_temp = "SELECT object_id, name FROM temp_example"

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

JesseTemplin2 — Thu, 14 Jan 2021 20:35:08 GMT

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.