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

[GISDepartmentMFM]

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).

[ChrisRingo]

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

[GISDepartmentMFM]

thank you for the tip the formatting has be changed

[GISDepartmentMFM]

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

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).

[Kara_Shindle]

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

[GISDepartmentMFM]

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).

[Kara_Shindle]

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.