ArcPy Search Cursor WHERE clause (PostGRES)

12-08-2020 07:09 AM
New Contributor II

I have a search cursor i am trying to use in a larger program and to spite my best efforts I cannot get it to work, I believe it has to do with the syntax of the WHERE clause within the search cursor. The kicker is I have another search cursor that does work on the same dataset .

I am trying to select from a POSTGRES database using the OBJECTID field, I have tried many different things but nothing every time i run it i am getting the below error

cannot open 'DATASET'
<type 'exceptions.RuntimeError'>

this is the text i am using in the search cusor that does work (field is a text field)

"STATUS = '1'"

This is what i currently have in the where clause of the search cursor that is not working:

"OBJECTID = " +object_id_value


I have tried implementing the field delimiters via the arcpy ( but got a goose egg on results.



0 Kudos
4 Replies
MVP Esteemed Contributor

Odd, nothing jumps out as obviously incorrect.  Not to ask the obvious, but is the ObjectID actually named OBJECTID?  Also, if the object_id_value string a numeric?  If numeric, you can't just concatenate it.   What if you hard code an ObjectID instead of building the expression.  Have you tried executing the SQL from outside ArcGIS using psql? 

New Contributor II

thank you for your response. It is named objectid, i have been staring at this field for a while now and its burned into my brain! I should have also mentioned that if I open the python window in Arc I can create the search query.

The only thing i can think of is that the OBJECTID value returned by my program is incorrect but i do not see how. all i am doing is getting that value then dropping it into the query.  

0 Kudos
MVP Esteemed Contributor

Are you sure the OBJECTID value is being returned as text?  It doesn't hurt to do an explicit type conversion that will cover both text and numeric values.

 "OBJECTID = " + str(object_id_value)
0 Kudos
New Contributor II

success sort of! so the below will work which is similar to what you suggested however, it only working inside of Arc (for example importing the classes into ArcMap) or in Visual Studio Code. it will not work in Idle for some reason, I have no idea why. 

so in short if anyone sees this issue try a different IDE before you start going crazy!

 "objectid = {0}".format(objectid_field))
Update that did not do it! 
I have been debugging my problem and I think the problem may have to do with the where clause in a search cursor. for context i have another search cursor that works on the same dataset, the only difference this one uses objectid where the other uses a text field. 
below is my function
def _select_row_from_table(selfsde_connection_pathtable_namefieldswhere_clause😞
    with arcpy.da.SearchCursor(os.path.join(sde_connection_path, table_name), fields, where_clause) as table_cursor:
        for a_row in table_cursor:
            row = dict()
            idx = 0
            for field_name in fields:
                row[field_name] = a_row[idx]
                idx = idx + 1
            return row
the where clause is this currently. i have tried many many things and nothing seems to work. 
'objectid = 99'
this is not the only thing i have tried, its just the most recent. 
reminder this is a POSTGRES dataset.
any help would be apricated thanks! 
0 Kudos