Python - Select by attributes query issue

4632
9
02-04-2015 05:53 AM
MatthewRusso
New Contributor II

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?

0 Kudos
9 Replies
Luis_ÁngelPascual_Camino
New Contributor III

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

0 Kudos
Luis_ÁngelPascual_Camino
New Contributor III

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

0 Kudos
MatthewRusso
New Contributor II

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
Esri Esteemed Contributor

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
MVP Regular Contributor

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

Map two lists into a dictionary in Python - Stack Overflow

0 Kudos
AnthonyGiles
Frequent Contributor II

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
New Contributor II

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.

0 Kudos
BlakeTerhune
MVP Regular Contributor

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!