Select to view content in your preferred language

Select Layer By attribute with a variable and null values

3920
5
Jump to solution
07-25-2013 01:36 PM
ionarawilson1
Deactivated User
The following snippet works fine if there are no null values for the SequenceNumber field on the table, but it crashes if there are any. Is there any way to change this query? I am thinking that null values cannot be used as strings? Is there any way to create this selection query keeping the maxValue4 an integer? Thank you!
 if  result > 1:         maxValue4 = arcpy.SearchCursor("TMP", "", "", "", "SequenceNumber D").next().getValue("SequenceNumber")         arcpy.AddMessage(str(maxValue4))         query = "SequenceNumber = "         arcpy.SelectLayerByAttribute_management("TMP",  "SUBSET_SELECTION", "SequenceNumber = " +  str(maxValue4))     result = int(arcpy.GetCount_management("TMP").getOutput(0))
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
RhettZufelt
MVP Notable Contributor
Ionara,

This will do that for you.

import arcpy, os from arcpy import da  ws = r'D:\update\wch_updated2.gdb' arcpy.env.workspace = ws arcpy.env.overwriteOutput = True   build_lyr = "build_lyr" field = "rec" maxValue = 14  arcpy.MakeFeatureLayer_management("buildings", "build_lyr")  sqlFieldName = arcpy.AddFieldDelimiters("build_lyr", field) expr = sqlFieldName + " = " + str(maxValue)  arcpy.SelectLayerByAttribute_management ("build_lyr", "NEW_SELECTION", expr)   with da.SearchCursor(build_lyr,['rec']) as cursor:   for row in cursor:     print str(row[0])


Of course, the SearchCursor is just so that I could tell if it was working or not....

Have not tested with nulls as I have a script that removes all them, and sets the fields to not nullable as I got tired of issues with them.

R_

View solution in original post

0 Kudos
5 Replies
ionarawilson1
Deactivated User
It seems to be working now. I am not sure why. I am guessing something with spaces. But if anybody could tell me how to create queries with integer variables it would be great! Thanks

                                                                                                                                                                  arcpy.SelectLayerByAttribute_management("TMP",  "SUBSET_SELECTION", "SequenceNumber = " + str(maxValue4))
0 Kudos
RhettZufelt
MVP Notable Contributor
Ionara,

This will do that for you.

import arcpy, os from arcpy import da  ws = r'D:\update\wch_updated2.gdb' arcpy.env.workspace = ws arcpy.env.overwriteOutput = True   build_lyr = "build_lyr" field = "rec" maxValue = 14  arcpy.MakeFeatureLayer_management("buildings", "build_lyr")  sqlFieldName = arcpy.AddFieldDelimiters("build_lyr", field) expr = sqlFieldName + " = " + str(maxValue)  arcpy.SelectLayerByAttribute_management ("build_lyr", "NEW_SELECTION", expr)   with da.SearchCursor(build_lyr,['rec']) as cursor:   for row in cursor:     print str(row[0])


Of course, the SearchCursor is just so that I could tell if it was working or not....

Have not tested with nulls as I have a script that removes all them, and sets the fields to not nullable as I got tired of issues with them.

R_
0 Kudos
ionarawilson1
Deactivated User
Thank you Rhett! Can you share with me the code snippet that sets the fields to not nullable? I have been trying to figure that out for a while!!!
0 Kudos
RhettZufelt
MVP Notable Contributor
Sure, unfortunalty, you can't just change a field, so basically, it creates a field map, modifies it, then replaces the existing fields with the new "mapped" fields.

This one is set to work within all DS's within a FGDB, could easily be modified to only work on one FC, etc.

import arcpy, os

ws = r'D:\update\wch_updated.gdb'
arcpy.env.workspace = ws

datasets = arcpy.ListDatasets()

excludeList = ['Base','OSEGrids','River_Flows','Utilities','Wells','MR']


# Overwrite pre-existing files
arcpy.env.overwriteOutput = True 
datasets.append("")    # this appends a blank onto the dataset list so it picks up FC's in the base FGDB level.

for ds in datasets:
    if ds not in excludeList:               #  only do this to DS's not in my exclude list
        print "working on ",ds
        fcs =  arcpy.ListFeatureClasses("","",ds)

        for fc in fcs:      

            print "creating field maps"
            fieldmappings = arcpy.FieldMappings()

            fieldmappings.addTable(fc)

            for field in arcpy.ListFields(fc, "", "String"):

                #Find all fields other than type of ObjectId and Geometry

        #        if (field.type != 'OID') & (field.type != 'Geometry'):   # I commented this as my ListFields limits to "String" fields only

                    #Create a new Field Map Object and populate it

                    print "populating object for ",fc
                    fldmap_Changed = arcpy.FieldMap()

                    fieldName = field.name

                    fldmap_Changed.addInputField(fc, fieldName)

                    #Get a new field object from the Field Map Object and set the Allow Null Property to False

                    print "setting to false"
                    fld_Changed = fldmap_Changed.outputField

                    fld_Changed.isNullable = False

                    #Add the field back to the Field Map Object        

                    fldmap_Changed.outputField = fld_Changed

                    #Find and replace the current field map in the Field Mappings with the new Field Map Object       

                    print "replacing current field map"
                    index = fieldmappings.findFieldMapIndex(fieldName) 

                    fieldmappings.replaceFieldMap(index, fldmap_Changed)

                    del fldmap_Changed, fld_Changed

            print "outputting fc ",fc
            arcpy.FeatureClassToFeatureClass_conversion(ds + os.sep + fc, arcpy.env.workspace, ds + os.sep + fc + "Copy1", "", fieldmappings)

####
###   The script makes a copy in each dataset of the original.
###   This section removes the original and renames the copy to the original name
####
            for fc in fcs:
             if arcpy.Exists(ds + os.sep + fc):
                print "deleting ",ds + os.sep + fc
                arcpy.Delete_management(ds + os.sep + fc)
                print "creating ",fc,"from ",ds + os.sep + fc + "Copy1"
                arcpy.FeatureClassToFeatureClass_conversion(ds + os.sep + fc + "Copy1", arcpy.env.workspace, ds + os.sep + fc, "")
                arcpy.Delete_management(ds + os.sep + fc + "Copy1")
                


del fieldmappings
arcpy.Compact_management(ws)   # compact FGDB to removed locks and optimize performance



R_
0 Kudos
ionarawilson1
Deactivated User
Cool!!! Thank you Rhett! This is really helpful!!!
0 Kudos