0

Basic arcpy script (ArcMap 10.8), I'm trying to add field delimiters for a query, however it's adding double-quotes " " instead of square brackets [ ] around my field name, which then makes my query fail, as the feature class is in a personal geodatabase which requires the square brackets.

>>> mxd = arcpy.mapping.MapDocument("CURRENT")
>>> layername = "my_layer"
>>> layer = arcpy.mapping.ListLayers(mxd, layer_name)[0]
>>> print layer
my_layer

>>> field_delimiter = arcpy.AddFieldDelimiters(layer, "OBJECTID")
>>> print field_delimiter
"OBJECTID"

I have realised that I can pass the path to the actual feature class to the AddFieldDelimiters() tool, so this is my current work-around solution:

>>> field_delimiter = arcpy.AddFieldDelimiters(r"C:\Temp\mygdb.mdb\MyFC", "OBJECTID")
>>> print field_delimiter
[OBJECTID]

However, I'd like to know if I'm doing something wrong in the syntax of the layer based approach, and how to fix it. If I continue with the workaround I'll have to include extra functionality to determine what the path to the data actually is (which is fine I guess, just adds further steps to the process)

How can I use the AddFieldDelimiters() on a layer within my MXD?


As requested in comments, here is working test version of my code, including the query, that was failing because of the " " instead of [ ]

import arcpy
layer = "Layer_in_MXD"
selected_oid = 36
query = "{} <> {}".format(arcpy.AddFieldDelimiters(layer, arcpy.Describe(layer).OIDFieldName), selected_oid)
arcpy.AddMessage(query)
with arcpy.da.SearchCursor(layer, ["OID@", "SHAPE@"], query) as cursor:
    for row in cursor:
        row_oid, row_geom = row

which outputs the following:

Executing: xTestPython
Start Time: Wed Feb 21 06:17:24 2024
Running script xTestPython...
"OBJECTID" <> 36
Failed script xTestPython...

Traceback (most recent call last):
  File "C:\Test\testpython.py", line 8, in <module>
    for row in cursor:
RuntimeError: Data type mismatch in criteria expression.

Failed to execute (xTestPython).
Failed at Wed Feb 21 06:17:25 2024 (Elapsed Time: 0.36 seconds)

If I make the query use square brackets (as Esri documentation says Personal Geodatabases require square brackets) then the query works without issue. This is done either by hard-coding the square brackets (not a long-term solution as the source data won't always be Personal GDB), or by making the Add Field Delimiters first detect the full source path of the layer (my current work-around)

Executing: xTestPython
Start Time: Wed Feb 21 06:20:47 2024
Running script xTestPython...
[OBJECTID] <> 36
Completed script xTestPython...
Succeeded at Wed Feb 21 06:20:47 2024 (Elapsed Time: 0.41 seconds)

From Esri Docs for Add Field Delimiters:

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotation marks (" "), personal geodatabases use square brackets ([ ]), and enterprise geodatabases don't use field delimiters. The function can take away the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones.

7
  • 1
    Personally, I never use AddFieldDelimiters() to construct queries. I think they were necessary for writing flexible data source type code until 5-10 years ago but then ArcMap started to support field names without delimiters in expressions and I think ArcGIS Pro has never required them.
    – PolyGeo
    Feb 19 at 19:36
  • One thing is that I may not have used a Personal Geodatabase in Access for more than 10 years so perhaps that’s the one workspace type where they are still needed
    – PolyGeo
    Feb 19 at 19:39
  • 1
    In ArcGIS for desktop you don't ned delimiters for a SQL expression in shapefiles (and dbIV tables), personal geodatabase and file geodatabase; I don't have enough experience with enterprise databases to say for sure one way or the other. In my experience (from ArcGIS 8.3 to the current version) the only time you need the delimeters is in the expression portion of Calculate Field. I can honestly say I've never used AddFieldDelimiters. Feb 20 at 0:50
  • 1
    @MichaelStimson I had a query keep failing in a search cursor. Once I changed from double quotes to square brackets (apparently needed by personal geodatabase) the query worked as desired. This caused me to try to use the Add delimiters tool to make it work no matter the database type. Perhaps there's another problem causing it all to not work
    – Midavalo
    Feb 20 at 2:57
  • I haven't experienced that, though to be fair I haven't worked with personal geodatabases for a while; ArcGIS Pro does not support personal geodatabases and the file size limit was restrictive. Can you edit your question to include a few lines up to the query being created and performed, a second (or more) set of eyes can't hurt as long as your code can be shared without causing you problems. Feb 20 at 3:13

1 Answer 1

1

I think that you should test this code, which does not use arcpy.AddFieldDelimiters(), because it is a simpler coding pattern and my understanding is that field delimiters have not been required for quite some time.

import arcpy
layer = "Layer_in_MXD"
selected_oid = 36
query = "{} <> {}".format(arcpy.Describe(layer).OIDFieldName, selected_oid)
arcpy.AddMessage(query)
with arcpy.da.SearchCursor(layer, ["OID@", "SHAPE@"], query) as cursor:
    for row in cursor:
        row_oid, row_geom = row
5
  • Thanks, I'll test that tomorrow... although I think that's what I had to start with, which lead to the error, which lead me to trying the AddFieldDelimiters. But too many iterations of the script ago to actually remember what I started with. Will update you when I've tested
    – Midavalo
    Feb 20 at 21:11
  • It just seems odd that they would still include AddFieldDelimiters() in 10.8 (and say that Personal GDB require square brackets) if in fact it's no longer needed... I'd prefer to not use it
    – Midavalo
    Feb 20 at 21:12
  • Leaving it in the code base does not surprise me because taking it out would break some people’s code. Not documenting that it’s no longer needed (assuming that’s the case) does surprise me.
    – PolyGeo
    Feb 20 at 21:45
  • query = "{} <> {}".format(arcpy.Describe(layer).OIDFieldName, selected_oid) did indeed work as desired. Now I'm very confused, but glad it's working 😎
    – Midavalo
    Feb 21 at 20:47
  • 1
    I just provided feedback on the documentation to Esri via the link on that page and cited this Q&A as an example of the confusion not mentioning that it's only present to support old code can cause.
    – PolyGeo
    Feb 21 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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