Python - Select by attributes query issue

[MatthewRusso]

import arcpy
code = ["007","013","014","015","017","019","021","023","027","029","031","033","035","037","039","041","043","045","047","049","051","055","059","063","065",
        "067","069","075","077","081","083","085","091","097","099","101","103","105","107","109","113","117","119","123","125"]
        
cnty = ["Archuleta","Boulder","Broomfield","Chaffee","Cheyenne","Clear_Creek","Conejos","Costilla","Custer","Delta","Denver","Dolores","Douglas","Eagle","Elbert","El_Paso","Fremont","Garfield",
        "Gilpin","Grand","Gunnison","Huerfano","Jefferson","Kit_Carson","Lake","La_Plata","Larimer","Logan","Mesa","Moffat","Montezuma","Montrose","Ouray","Pitkin","Prowers","Pueblo","Rio_Blanco",
        "Rio_Grande","Routt","Saguache","San_Miguel","Summit","Teller","Weld","Yuma"]
        
try:
    arcpy.SelectLayerByAttribute_management("Colorado_Clip","NEW_SELECTION",""""COUNTY_CODE" = code[0]""")
    arcpy.CopyFeatures_management("Colorado_Clip","H:/GIS DATA/Data/Conflation/RPAM Data NAD83.gdb/Colorado/" + cnty[0],"#","0","0","0")
    arcpy.SelectLayerByAttribute_management('Colorado_Clip', "CLEAR_SELECTION")
    del cnty[0]
    del code[0]
    print cnty
    print code  
except:
    print code[0] + " Failed"

Above is my simple script.

The goal is to loop through the items on the list, select attributees and create a new shapefile based on the name in the list, clear my selection, del that item from the list and move on to the next one.

Problem I am running into is in line 10 where the {where_clause} is.  The tool does not want to take a list item for the value (code[0]) is not acceptable.  Is there a way around this so i can use my list I don't want to have to type all of these out manually.

Orginially i wanted to split this file, but the size of the file prevents me from doing this.  I also have to do this for 8 more states.

Anyone have any ideas?

[Luis_ÁngelPascual_Camino]

Hi Matthew

Try something like this, with an iterator 'for', between the first position 0 and the 45th?? number 44.

It isn't necesary delete every item, the loop will read every position in every iteration.

import arcpy

code = ["007","013","014","015","017","019","021","023","027","029","031","033","035","037","039","041","043","045","047","049","051","055","059","063","065",

        "067","069","075","077","081","083","085","091","097","099","101","103","105","107","109","113","117","119","123","125"]

cnty = ["Archuleta","Boulder","Broomfield","Chaffee","Cheyenne","Clear_Creek","Conejos","Costilla","Custer","Delta","Denver","Dolores","Douglas","Eagle","Elbert","El_Paso","Fremont","Garfield",

        "Gilpin","Grand","Gunnison","Huerfano","Jefferson","Kit_Carson","Lake","La_Plata","Larimer","Logan","Mesa","Moffat","Montezuma","Montrose","Ouray","Pitkin","Prowers","Pueblo","Rio_Blanco",

        "Rio_Grande","Routt","Saguache","San_Miguel","Summit","Teller","Weld","Yuma"]

try:

  for i in range(0, 44)

  arcpy.SelectLayerByAttribute_management("Colorado_Clip","NEW_SELECTION",""""COUNTY_CODE""" = " + code)

  ## Verify your path, if your running in windows, use the first 'copy' statement with backslashes \

  #arcpy.CopyFeatures_management("Colorado_Clip","H:\GIS DATA\Data\Conflation\RPAM Data NAD83.gdb\Colorado\" + cnty,"#","0","0","0")

  arcpy.CopyFeatures_management("Colorado_Clip","H:/GIS DATA/Data/Conflation/RPAM Data NAD83.gdb/Colorado/" + cnty,"#","0","0","0")

  arcpy.SelectLayerByAttribute_management('Colorado_Clip', "CLEAR_SELECTION")

  #del cnty[0]

  #del code[0]

  print cnty

  print code

except:

    print code[0] + " Failed"

I hope it helps.

Luis

[Luis_ÁngelPascual_Camino]

Hi again.

Reviewing your issue, the error given by the 'select' function is due to the expression:

""""COUNTY_CODE" = code[0]"""

You'are passing to the function the literal "code[0]" that is an unknown variable for it. If you try:

""""COUNTY_CODE""" = " + code[0]

I think that will run ok with your original code because python will replace it with the value of the list in this position.

Good luck!

Luis

[MatthewRusso]

Luis,

Thanks for the reply this is giving me a headache!  I just played around and came up with this also,

field = "COUNTY_CODE"
where_clause = ("'" + field + "'" + "= " + code[0])
print where_clause
'COUNTY_CODE'= 007

If that doesn't work I will go the route you mentioned and get back to you.

[JoshuaBixby]

You might want to try:

field = "COUNTY_CODE"
where_clause = "{} = '{}'".format(field, code[0])

or

where_clause = "COUNTY_CODE = '{}'".format(code[0])

You don't need to put your field name in quotes, but the value itself will need to be in quotes if it is text.

I strongly encourage you to read up on Python String Formatting.  It is much more powerful and Pythonic for building SQL expressions than concatenating strings together.

[XanderBakker]

In addition to what Joshua Bixby‌ shows, you could also include logic that will work no matter what the workspace is. Remember a where clause is different depending on the workspace (shapefiles, personal gdb, file geodatabase...)

where ="{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_code), code)

To create the dictionary you can do this:

dct = {}
for i in range(len(code)):
    dct[code] = cnty
print dct

Next you copy and paste the dct behind "dct = " and you have your dict:

dct = {'077': 'Mesa', '075': 'Logan', '023': 'Costilla', '099': 'Prowers',
       '091': 'Ouray', '097': 'Pitkin', '117': 'Summit', '039': 'Elbert',
       '014': 'Broomfield', '015': 'Chaffee', '113': 'San_Miguel',
       '017': 'Cheyenne', '055': 'Huerfano', '031': 'Denver', '119': 'Teller',
       '051': 'Gunnison', '035': 'Douglas', '123': 'Weld', '029': 'Delta',
       '037': 'Eagle', '083': 'Montezuma', '019': 'Clear_Creek',
       '033': 'Dolores', '063': 'Kit_Carson', '065': 'Lake', '067': 'La_Plata',
       '069': 'Larimer', '081': 'Moffat', '085': 'Montrose', '027': 'Custer',
       '109': 'Saguache', '007': 'Archuleta', '125': 'Yuma', '049': 'Grand',
       '047': 'Gilpin', '103': 'Rio_Blanco', '045': 'Garfield', '101': 'Pueblo',
       '043': 'Fremont', '107': 'Routt', '041': 'El_Paso', '105': 'Rio_Grande',
       '013': 'Boulder', '021': 'Conejos', '059': 'Jefferson'}

to loop through the dict as shown before, use this:

for code, cntr in dct.items():
    where ="{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_code), code)

[BlakeTerhune]

Although I haven't used it yet, the zip() function should work well too.

Map two lists into a dictionary in Python - Stack Overflow

[AnthonyGiles]

Matthew,

Instead of using two arrays holding your values I would suggest using a dictionary:

Python — Basics of Python Dictionary: Looping & Sorting | YUJI TOMITA

Eg:

codes = ['007': 'Archuleta','013': 'Boulder','014': 'Broomfield']

That way your county codes and names are linked to each other and you can just loop through your dictionary:

for key,val in codes.items():

print key,val

[MatthewRusso]

Thanks for all the input everyone.

I'm sure i ended up doing a very primitive way but it is a time sensitive matter and I am very green at Python.

I ended up getting my SQL statement to work like this:

field = "COUNTY_CODE"
where_clause = ('"' + field + '"' + " = " + "'" + code[0] +"'")

I will look into the dictionaries and everything else because it seems like the way to go for the next sets of states.

[BlakeTerhune]

When doing this with very long lists, I find it helpful to also format the code so it's more readable. Here I organized it by the first letter.

cnty = [
    "Archuleta",
    "Boulder","Broomfield",
    "Chaffee","Cheyenne","Clear_Creek","Conejos","Costilla","Custer",
    "Delta","Denver","Dolores","Douglas",
    "Eagle","Elbert","El_Paso",
    "Fremont",
    "Garfield","Gilpin","Grand","Gunnison",
    "Huerfano",
    "Jefferson",
    "Kit_Carson",
    "Lake","La_Plata","Larimer","Logan",
    "Mesa","Moffat","Montezuma","Montrose",
    "Ouray",
    "Pitkin","Prowers","Pueblo",
    "Rio_Blanco","Rio_Grande","Routt",
    "Saguache","San_Miguel","Summit",
    "Teller",
    "Weld",
    "Yuma"
]  # End cnty list

You could also organize by any other logical groups with comments to make maintenance easier.

code = [
## Some Group
    "007","013","014","015","017","019","021","023",
## Another Group
    "027","029","031","033","035","037","039","041","043","045","047","049",
## Third Group
    "051","055","059","063","065","067","069",
## More Grouping
    "075","077","081","083","085","091","097","099",
## Still Grouping
    "101","103","105","107","109","113","117","119","123",
## Last Group
    "125"
]  # End code list

And it works the same way with dictionaries!