topic Re: How to create a unique list of selected values from a field, Python and SQL in Python Questions

How do I create a unique list of selected values from a field? Python and SQL

GISDepartmentMFM — Thu, 18 May 2023 18:11:31 GMT

What I am trying to do is to have create a list of unique TAZs from the data already selected and iterate over that list and subselect all the rows that have that taz then check if they have the
Exclude identifier in the Exclude field, if so then remove 1 from the number of points else don't, have that iterate until there are no more values left in the list,
I tried inputting the below code into a Chat bot and it came out with the second code block however
it produces an error provided and don't know if the syntax is right as I only started learning Python and SQL yesterday, could someone help me figure out how to do this?

What I Gave

import arcpy #geoprocesing layer creation #repair the geometry arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL') print("fetures repaired") #create points arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE') print("points layer created") #Summarize points and feture layer arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints') print("SummerizedPoints created") #selections and calulations of the data # add calculation field arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE') print("Area_Per_Point feild created") #cleaning data #selection of all the condos arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2') #obtain list of TAZs code goes here #loop through the list of TAZ's selecting each group of same TAZ each time for value < count of unique TAZ's; value++ { #select the current value of TAZ arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value') if ("Exclude = 'Exclude'"){ #remove excluded arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'") #calculate the area of each point if excluded arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3') } #if there are not any excludes arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3') } print("calculations compleate") #clear out slection arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION') print("Program Done")

Chat Bot

import arcpy #geoprocesing layer creation #repair the geometry arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL') print("fetures repaired") #create points arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE') print("points layer created") #Summarize points and feture layer arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints') print("SummerizedPoints created") #selections and calulations of the data # add calculation field arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE') print("Area_Per_Point feild created") #cleaning data #selection of all the condos arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2') # Obtain list of TAZs unique_taz_values = [] with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor: for row in cursor: if row[0] not in unique_taz_values: unique_taz_values.append(row[0]) # Loop through the list of TAZs, selecting each group of the same TAZ each time for taz_value in unique_taz_values: #select the current value of TAZ arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value') if ("Exclude = 'Exclude'"): #remove excluded arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'") #calculate the area of each point if excluded arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3') else: #if there are not any excludes arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3') print("calculations compleate") #clear out slection arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION') print("Program Done")

Error Given
Traceback (most recent call last):
File "<string>", line 12, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11294, in SelectLayerByAttribute
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11291, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 160144: An expected Field was not found or could not be retrieved properly.
Failed to execute (SelectLayerByAttribute).

How to create a unique list of selected values from a field, Python and SQL

GISDepartmentMFM — Thu, 18 May 2023 18:13:37 GMT

What I am trying to do is to have create a list of unique TAZs from the data already selected and iterate over that list and subselect all the rows that have that taz then check if they have the
Exlcude identifyer in the Exclude field, if so then remove 1 from the number of points else dont, have that iterate untille there are no more values left in the list,
I tryed inputing the below code into a Chat bot and it came out with the second code block however
it produces an error providud and dont know if the syntax is right as I only started learning Python and SQL yesterday, could someone help me figure out how to do this?

What I Gave

Chat Bot

import arcpy #geoprocesing layer creation #repair the geometry arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL') print("fetures repaired") #create points arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE') print("points layer created") #Summarize points and feture layer arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints') print("SummerizedPoints created") #selections and calulations of the data # add calculation field arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE') print("Area_Per_Point feild created") #cleaning data #selection of all the condos arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2') # Obtain list of TAZs unique_taz_values = [] with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor: for row in cursor: if row[0] not in unique_taz_values: unique_taz_values.append(row[0]) # Loop through the list of TAZs, selecting each group of the same TAZ each time for taz_value in unique_taz_values: #select the current value of TAZ arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value') if ("Exclude = 'Exclude'"): #remove excluded arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'") #calculate the area of each point if excluded arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3') else: #if there are not any excludes arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3') print("calculations compleate") #clear out slection arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION') print("Program Done")

Re: How do I create a unique list of selected values from a field? Python and SQL

ChrisRingo — Thu, 18 May 2023 18:02:19 GMT

It will be easier for folks to address your question if you format your code. Please see these guidelines - https://community.esri.com/t5/community-help-documents/how-to-insert-code-in-your-post/ta-p/914552

Re: How do I create a unique list of selected values from a field? Python and SQL

GISDepartmentMFM — Thu, 18 May 2023 18:12:09 GMT

thank you for the tip the formatting has be changed

Re: How to create a unique list of selected values from a field, Python and SQL

Kara_Shindle — Fri, 19 May 2023 13:12:04 GMT

Are you copying the code exactly as it appears in the box from the chat box? Your search cursor has some loops in it that are not formatted right, and if the indents aren't in there, it's not going to run right.

import arcpy #geoprocesing layer creation #repair the geometry arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL') print("fetures repaired") #create points arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE') print("points layer created") #Summarize points and feture layer arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints') print("SummerizedPoints created") #selections and calulations of the data # add calculation field arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE') print("Area_Per_Point feild created") #cleaning data #selection of all the condos arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2') # Obtain list of TAZs unique_taz_values = [] with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor: for row in cursor: if row[0] not in unique_taz_values: unique_taz_values.append(row[0]) # Loop through the list of TAZs, selecting each group of the same TAZ each time for taz_value in unique_taz_values: #select the current value of TAZ arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value') if ("Exclude = 'Exclude'"): #remove excluded arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'") #calculate the area of each point if excluded arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3') else: #if there are not any excludes arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3') print("calculations complete") #clear out slection arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION') print("Program Done")

You should also be deleting your cursors when you're done to clean up memory, so at the end of this you would have something like:

del cursor

Re: How to create a unique list of selected values from a field, Python and SQL

GISDepartmentMFM — Fri, 19 May 2023 14:36:45 GMT

not sure why there is a replay with the post, but i am having a new issue where I cant get a python variable into the SQL query

this is the new code

import arcpy #geoprocesing layer creation #repair the geometry arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL') print("fetures repaired") #create points arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE') print("points layer created") #Summarize points and feture layer arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints') print("SummerizedPoints created") #selections and calulations of the data # add calculation field arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE') print("Area_Per_Point feild created") #cleaning data #selection of all the condos arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2') # Obtain list of TAZs Unique_values = list(set(row[0] for row in arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG'))) # Loop through the list of TAZs, selecting each group of the same TAZ each time for taz_value in Unique_values: #select the current value of TAZ sql_query = f"TAZ_REG = '{taz_value}'" arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', sql_query) if ("Exclude = 'Exclude'"): #remove excluded arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'") #calculate the area of each point if excluded arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3') else: #if there are not any excludes arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3') print("calculations compleate") #clear out slection del cursor arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION') print("Program Done")

The error, the error line is arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', sql_query)

Traceback (most recent call last): File "<string>", line 10, in <module> File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11294, in SelectLayerByAttribute raise e File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11291, in SelectLayerByAttribute retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True))) File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda> return lambda *args: val(*gp_fixargs(args, True)) arcgisscripting.ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

Re: How to create a unique list of selected values from a field, Python and SQL

Kara_Shindle — Fri, 19 May 2023 15:15:05 GMT

Seems like it has an issue with your SQL query. Your code is below:

sql_query = f"TAZ_REG = '{taz_value}'"

See a working example below:

whereClause = "TAX_DIST = '" + str(taxDistrict) + "'"

Why do you have a "f" at the beginning of your query?

Also I believe you need to rework your quotes. I would suggest testing your query by adding a print statement after it - you may see that your quotes are not getting inserted as Text

Update:

So testing this small bit of code:

taxDistrict = 12 whereClause = "TAX_DIST = '" + str(taxDistrict) + "'" print(whereClause)

I get this result printed to the console:

TAX_DIST = '12'

You can also try building the query in ArcPro, switching to the SQL view & using that to help check your syntax.