[GIS] Selecting Layer by Attribute using SQL and variable in ArcPy

arcgis-10.0arcpyselect-by-attribute

I've had a good look around the site and haven't been able able to find any 'select by attribute' questions regarding string variables and how to integrate them into an SQL statement. In my particular case I have used a search cursor to create a variable but cannot find a way to use that variable in the select by attributes tool. Below is an example of one of the many ways I have tried to select the variable.

The variable I've mentioned (point_id) is the string EDRN_044851.

rows = arcpy.SearchCursor("feature_layer11")
for row in rows:
    point_id = row.getValue("DRN_update")

arcpy.MakeFeatureLayer_management("EDRN_NODE", "feature_layer15")
arcpy.SelectLayerByAttribute_management ("feature_layer15", "NEW_SELECTION", '\"LCC_DRN_ID\" = {0}'.format(point_id))

desc1 = arcpy.Describe("feature_layer15")
shapefieldname1 = desc1.ShapeFieldName
rows = arcpy.SearchCursor("feature_layer15")
for row in rows:
    feat = row.getValue(shapefieldname1)
    pnt = feat.getPart()
    pnt_x = pnt.X
    pnt_y = pnt.Y

Best Answer

It always helps to include the error message you're getting with the script, so I'm guessing a bit, but the correct format for your SQL where clause is probably

'"LCC_DRN_ID" = \'{0}\''.format(point_id)

Note that you don't have to escape the double quotes with the slash because you're using single quotes for the overall SQL string, but you do need to escape any inner single quotes, which is what I did around {0}.

Also, the correct field delimiter is not always double quotes - it depends on the data source. If you're using a personal geodatabase for example the field delimiter is square brackets [LCC_DRN_ID]. You can use the AddFieldDelimiter tool to add them in dynamically, if you're not sure of your datasource ahead of time http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v0000004n000000

Related Solutions

Arcpy Select Analysis – Including Variable in WHERE Clause of Arcpy.Select_analysis()

One thing that makes writing WHERE clauses a lot easier is to use the AddFieldDelimiters function, which automatically adds the correct, DBMS-specific delimiters for field identifiers, such as double-quotes for FGDB and brackets for PGDB.

The other thing you have to consider is whether the value is a number, string, or other data type. Specifically, strings are wrapped in single quotes while numbers are not. You could check the field type and add single quotes if it is a string field.

E.g.:

import arcpy

def buildWhereClause(table, field, value):
    """Constructs a SQL WHERE clause to select rows having the specified value
    within a given field and table."""

    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(table, field)

    # Determine field type
    fieldType = arcpy.ListFields(table, field)[0].type

    # Add single-quotes for string field values
    if str(fieldType) == 'String':
        value = "'%s'" % value

    # Format WHERE clause
    whereClause = "%s = %s" % (fieldDelimited, value)
    return whereClause

if __name__ == "__main__":
    inputfc = r"C:\input.shp"
    outputfc = r"C:\output.shp"
    fieldname = "StudyID"
    fieldvalue = 101
    whereclause = buildWhereClause(inputfc, fieldname, fieldvalue)
    arcpy.Select_analysis(inputfc, outputfc, whereclause)

See also the function in this answer for a multi-value version of the above function.

[GIS] arcpy; Select by attribute with an If, then statement

Let's say you have a list of shapefiles and the field name for the attribute is the same in each shapefile. That means you can make the SQL query in the beginning of the script, so the first bit could look like this:

search_id = arcpy.GetParameterAsText(0)

shps = [r"path\to\shp1.shp",r"path\to\shp2.shp"]
output_shp = r"path\to\output.shp"
field = "ID_FIELD_NAME"

sql = '"{0}" = \'{1}\''.format(field,search_id)

Here are two ways to go about your selection process. The first uses a where clause in the MakeFeatureLayer() and GetCount(), and the second uses a SearchCursor() to find the attribute and proceeds to MakeFeatureLayer(). I'm not sure which one is faster, but my guess is the second. Most likely, the speed is very similar if you don't have tons of shapefiles.

for shp in shps:

    # make feature layer
    if arcpy.Exists("fl"):
        arcpy.management.Delete("fl")
    fl = arcpy.management.MakeFeatureLayer(shp,"fl",sql)

    # check count of features in new feature layer, skip to next shp if it == 0
    if int(arcpy.management.GetCount(fl).getOutput(0)) == 0:
        continue

    # copy features, only happens if there is a feature in the feature layer
    arcpy.management.CopyFeatures(fl,output_shp)

and here's the second. In this one, a SearchCursor is used to check for the presence of the feature, and then the MakeFeatureLayer() is used. I think this would probably be the faster way:

for shp in shps:

    # use list comprehension with a SearchCursor to check for the id
    all_ids = [r[0] for r in arcpy.da.SearchCursor(shp,field)]
    if not search_id in all_ids:
        continue

    # now make feature layer and copy features
    if arcpy.Exists("fl"):
        arcpy.management.Delete("fl")
    fl = arcpy.management.MakeFeatureLayer(shp,"fl",sql)

    # copy features
    arcpy.management.CopyFeatures(fl,output_shp)

EDIT:

With differing field names, you can store them and iterate the shapefiles like this:

shps = [(r"path\to\shp1.shp","ID_FIELD_NAME1"),
        (r"path\to\shp2.shp","ID_FIELD_NAME2")]
output_shp = r"path\to\output.shp"

for item in shps:
    shp = item[0]
    field = item[1]
    sql = '"{0}" = \'{1}\''.format(field,search_id)

    #continue with the rest of the code as above

Best Answer

Related Solutions

Arcpy Select Analysis – Including Variable in WHERE Clause of Arcpy.Select_analysis()

[GIS] arcpy; Select by attribute with an If, then statement

Related Question