topic Re: da.SearchCursor: where_clause is not respected in Python Questions

da.SearchCursor: where_clause is not respected

Nicole_Ueberschär — Fri, 10 Dec 2021 21:34:53 GMT

Hello,

I'm still working on my profiles with points etc.

Now I am facing a problem with my da.SearchCursor. While it was working well with my original tables, it is not respecting my where_clause any longer since I reduced the tables to the attributes I actually need.

def store_table(table):
    arcpy.AddMessage("open "+table)

    #get field names
    desc = arcpy.Describe(table)
    fieldnames=[field.name.encode("utf-8") for field in desc.fields]

    #get profile numbers
    profile_nrs=unique_values(table, "Profile No")
    arcpy.AddMessage(profile_nrs)

    tablefile=location_for_tables+"\\"+table #modified table
    # Select rows by station nr and save to txt file
    for profile in profile_nrs:
        if profile is not None and profile!=0:
            expression="'Profile No'='"+str(profile)+"'"

            arcpy.AddMessage(expression)
            with arcpy.da.SearchCursor(table,"*",where_clause=expression) as sCursor:
                for sRow in sCursor:
                    table_name=str(sRow[2])+"_"+str(sRow[3])+"_.txt" #Campaign + Profile_No
                    arcpy.AddMessage("table_name="+table_name)
                    filepath=DirOutTables +"\\"+ table_name   #seperated tables
                    UIDvalue=str(sRow[2])+"_"+str(sRow[3])
                    UIDGraphValue=UIDvalue+"_"
                    break
                if arcpy.Exists(filepath):
                    arcpy.AddMessage("Table "+table_name+" exists")
                else:
                    with open(tablefile, 'r') as t:
                        with open(filepath, 'a') as f:
                            w = csv.writer(f, delimiter="\t", lineterminator='\n')
                            r = csv.reader(t, delimiter="\t")
                            allnewtable=[]

                            theader=r.next()
                            theader.append('UID')
                            theader.append('UIDGraph')
                            allnewtable.append(theader)
                            arcpy.AddMessage(theader)

                            for sRow in sCursor:
                                sRowNew=sRow + (UIDvalue,UIDGraphValue)
                                allnewtable.append(sRowNew)
                            w.writerows(allnewtable)

                    del sRow
                    print("Table "+table_name+" created")
                    arcpy.AddMessage("Table "+table_name+" created")
        else:
            print("empty line")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In my test file I have 4 profiles in profile_nrs and with each loop through "for profile in profile_nrs" the expression is iterated correctly. But obviously the where_clause is not respected because sRow[3] is always 1. Also when I hard-code the expression with where_clause="'Profile_No'='4'" it still gives me back the whole table.

I thought maybe I used the sCursor already in another part of the script but that's not the case.

I tried the expression without putting the number in quotation marks but then it's not a valid sql expression it says.

There is no error message and the script runs through up to the end, with the only mistake that it creates only one table instead of four.

Do you have an idea what goes wrong?

Thanks in advance!

Re: da.SearchCursor: where_clause is not respected

DanPatterson_Retired — Thu, 05 Oct 2017 09:31:14 GMT

is this being run inside a tool? since you are only using AddMessage to report the various messages

Re: da.SearchCursor: where_clause is not respected

Nicole_Ueberschär — Thu, 05 Oct 2017 10:23:47 GMT

Yes, exactly.

Re: da.SearchCursor: where_clause is not respected

DanPatterson_Retired — Thu, 05 Oct 2017 10:31:05 GMT

so I presume the table name is being created properly, but what about the information you want? For example, have you reported the results of such lines as

UIDvalue=str(sRow[2])+"_"+str(sRow[3])

to see if they are returning the right values?

Re: da.SearchCursor: where_clause is not respected

Nicole_Ueberschär — Thu, 05 Oct 2017 11:10:28 GMT

Since the table name and the UIDvalue is created from the first row of the selected entries I always get the same table name "Campaignname_1_.txt". That is where I see at the first glance that something cannot be right. What I expect to see through the AddMessages is something like

'Profile No'='1'

table_name=Campaignname_1_.txt

'Profile No'='2'

table_name=Campaignname_2_.txt

'Profile No'='3'

table_name=Campaignname_3_.txt

'Profile No'='4'

table_name=Campaignname_4_.txt

But what I get is

'Profile No'='1'

table_name=Campaignname_1_.txt

'Profile No'='2'

table_name=Campaignname_1_.txt

'Profile No'='3'

table_name=Campaignname_1_.txt

'Profile No'='4'

table_name=Campaignname_1_.txt

Because it always reads the whole file and not my selection.

So something must be wrong with my expression or my sCursor definition I assume...

Re: da.SearchCursor: where_clause is not respected

DanPatterson_Retired — Thu, 05 Oct 2017 12:02:27 GMT

perhaps you mean to use a 'select by attributes' instead to make a selection then process that selection. If you are using a searchcursor, then you will inevitably be cycling through the rows.

Re: da.SearchCursor: where_clause is not respected

Nicole_Ueberschär — Fri, 06 Oct 2017 08:40:41 GMT

But isn't this what the where_clause is meant for? The same structure worked for me for different tables before but not since I edited the tables. It seems that the sql statement is valid but it returns no entries. I would assume that it then would also not return any lines from the table but interestingly I can put any statement (even if the attribute name does not exist) as long as the syntax is correct and it will always return the whole table. Is there a way to check for the result of the query before proceeding?

Re: da.SearchCursor: where_clause is not respected

Nicole_Ueberschär — Fri, 06 Oct 2017 09:03:35 GMT

Tataaa:

Looking through other postings here I found this way to write the expression:

expression = u'{} = {}'.format(fieldnames[3],str(profile))‍

instead of

expression="'"+fieldnames[3]+"'='"+str(profile)+"'"‍‍

and it works!

I would still be interested if there is a way to check the result of a query if someone has an idea.