topic Re: Select Layer By attribute with a variable and null values in Python Questions

Select Layer By attribute with a variable and null values

ionarawilson1 — Thu, 25 Jul 2013 20:36:55 GMT

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))

Re: Select Layer By attribute with a variable and null values

ionarawilson1 — Thu, 25 Jul 2013 20:56:37 GMT

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))

Re: Select Layer By attribute with a variable and null values

RhettZufelt — Thu, 25 Jul 2013 22:17:10 GMT

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_

Re: Select Layer By attribute with a variable and null values

ionarawilson1 — Fri, 26 Jul 2013 15:21:53 GMT

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!!!

Re: Select Layer By attribute with a variable and null values

RhettZufelt — Sun, 12 Dec 2021 07:06:36 GMT

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

Re: Select Layer By attribute with a variable and null values

ionarawilson1 — Fri, 02 Aug 2013 19:43:57 GMT

Cool!!! Thank you Rhett! This is really helpful!!!