nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Find more posts tagged with

Accepted answers

All comments

blake.terhune

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!

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.

AntGiles

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',</CODE><CODE class="python string">'013': 'Boulder',</CODE><CODE class="python string">'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

JoshuaBixby

You might want to try:

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

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.

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.

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

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.