0

I am developing an ArcPy script in ArcGIS Desktop 10.7 and using a file geodatabase feature class. I have a field called facility_id. I have tried using this field as a string field or as an integer field but have been miserably unsuccessful at using this field as a value in the arcpy.da.SearchCursor function. For my latest attempt I generate the value in facility_id from an integer field called x.

x = 123456
facility_id = "'" + str(x) + "'"
print facility_id gives '123456'

I would like to do this:

cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '$facility_id'")

(ASUFAC is a string field) When I execute this line of code, nothing is selected. If I execute the following line of code, one record is selected from the feature class, and is correct:

cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '123456'")

What is the correct syntax to use in order to have a value from the facility_id field be evaluated correctly in the where clause of the arcpy.da.SearchCursor function?

I've reviewed the thread "Using numeric variable in where clause of Arcpy SearchCursor" and adapted what was given there but it only generates an error: Invalid SQL statement was used

closed as unclear what you're asking by user2856, whyzar, Fran Raga, Mapperz May 9 at 1:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Assuming AUSFAC is the same field type as facility_id then cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = facility_id") should work. However consider using a with block to close the cursor properly when you're done with it with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = facility_id") as cursor: then for row in cursor: – Michael Stimson May 1 at 23:20
1

As ASUFAC is a string field, if facility_id is also a string field you can just use:

with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = facility_id") as cursor:
    for point, in cursor:
        etc...

If facility_id is an integer field, the documentation suggests you can use CAST to convert data type on the fly.

I couldn't get it to work (Invalid SQL statement was used) casting from integer to string (VARCHAR or CHAR data types), but it worked fine casting the string field to integer.

with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "CAST(ASUFAC AS INTEGER) = facility_id") as cursor:
    for point, in cursor:
        etc...
  • user2856, Thank you for your suggestions. Both ASUFAC and facility_id are strings - ASUFAC is a string field from a fc and facility_id is a string assigned variable. I can't seem to get cursor.reset() with arcpy.da.SearchCursor(Facility_fc, ("SHAPE@XY"), "ASUFAC = facility_id") as cursor: for point, in cursor: print(point) ... Runtime error Traceback (most recent call last): File "<string>", line 1, in <module> NameError: name 'cursor' is not defined – ljl May 3 at 22:22
  • Sorry, the correct error is: Runtime error Traceback (most recent call last): File "<string>", line 2, in <module> RuntimeError: An expected Field was not found or could not be retrieved properly. [name of feature class] – ljl May 3 at 22:38
1

(NB: This answer addresses your example code where you're using a directly assigned facility_id variable, rather than a facility_id field. I misunderstood your question when I wrote this answer. So it may not directly answer your original question, but I've left it here in case it is of use.)

You have included TWO sets of single quotes around your facility_id value. Fistly in your facility_id = "'" + str(x) + "'" and then later in"ASUFAC = '$facility_id'"`.

Even if the interpolation worked, you would end up with:

cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = ''123456''")

It looks like you're trying to use the dollar-sign ($) for variable interpolation. This is how it's done in some other languages (such as PERL), but not Python.

There are several ways to do variable interpolation in Python. I prefer the curly braces ({ & }) and the string format() method. Try it like this:

facility_id = 123456
cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '{}'".format(facility_id))

Or if ASUFAC is a number and not a string, then you should leave out the single quotes like this:

facility_id = 123456
cursor = arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = {}".format(facility_id))

The string format() method converts the string by looking for curly brackets and substituting them for the parameters passed in. There are a lot of other options that you can use, but in its simplest form, the curly bracket pairs will be substituted in the order of the parameters passed to format().

As an aside, I prefer using with for cursors, like this:

facility_id = 123456
with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '{}'".format(facility_id)) as cursor:
    for row in cursor:
        # do something with row here
  • Son of a Beach, Thanks very much for your response. Given: x = 587454 facility_id = str(x) This worked cursor = arcpy.da.SearchCursor(facility_fc, ("ASUFAC", "SHAPE@XY"), "ASUFAC = '{}'".format(facility_id)) for row in cursor: print(row) and this worked with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '{}'".format(facility_id)) as cursor: for point, in cursor: print(point) I don't understand the difference between – ljl May 3 at 22:44
0

Using @SonofaBeach's answer, and given:

x = 587454
facility_id = str(x) 

This worked for me:

cursor = arcpy.da.SearchCursor(facility_fc, ("ASUFAC", "SHAPE@XY"), "ASUFAC = '{}'".format(facility_id))
for row in cursor:
  print(row)

and this worked too:

with arcpy.da.SearchCursor(facility_fc, ("SHAPE@XY"), "ASUFAC = '{}'".format(facility_id)) as cursor:
  for point, in cursor:
    print(point)

I don't understand the difference between cursor = and with ... as cursor but I also freely admit that I'm trying to figure out how to automate a specific task by looking at examples and applying trial-n-error rather than trying to learn ArcPy per se.

Not the answer you're looking for? Browse other questions tagged or ask your own question.