Problems with da SearchCursor results from LIKE query

1522
18
01-23-2017 01:40 PM
ChrisStanley
New Contributor II

I have built an app with WxPython to run in Arcmap to search some tables that are already existing in ArcMap as a Python Addin.  I have a few fields in this table that will get results just fine and some fields that will get correct results and then no results from the same field with just different search values.  My search is a LIKE type query,  Ex, if I enter 55 as my search value, my WxPython app will not finish and open the PDF that has the results.  If I enter 61 my search will finish and open the PDF with the appropriate data in the report.  I do know there are several rows in my table that should be in the 355 search results.

If I run the code in the ArcMap Python window against the table with the 55 value I get the expected results with no problem from the cursor.  The only difference that I can see are the variables I am using in the query.  If I run it in the ArcMap Python window I am setting the variables manually to make sure the query is good.  In the WxPython App I am getting one variable from a selection made from a dropdown for the field "ESN" and the search value is from a text box where the user enters the value, "55".

The examples below are from a script that I am testing in the ArcMap window and then the search that my WxPython App is using.  The syntax is all correct so the indentations may be different between the two.  My main issue is why can't I get results when I use "55" as my value and I can get results when I search "61" when there are values in the table that should get results when I search LIKE '%55%'.  Also this same thing is happening in the Community field when I search LIKE values in there as well.

I have tested several variations and other fields and most everything will return results but I can't see any reason these will not.

Any help would be GREATLY appreciated!!!

Thanks

Chris

Here is the snippet from my manual search that gives me the results I want:

test = 'ESN'

#Query to retreive data from gisMSAG for GeoSearch...
story = []
results=[]
fields = ['LOW','HIGH','DIR','STREET NAME','EOB','COMMUNITY', 'ZIP','ESN','SRNUM']
story.append(fields)
with arcpy.da.SearchCursor(fc3,[dir,sname,low,high,com,eob,esn,sr,zip,desc,rem],test+" LIKE UPPER('%55%')",sql_clause=(None,'ORDER BY '+sname+' ASC, '+low+' ')) as cursor5:
     for mg in cursor5:
          msagdir = mg[0]
          msagsname = mg[1]
          msaglow = mg[2]
          msaghigh = mg[3]
          msagcom = mg[4]
          msageob = mg[5]
          msagesn = mg[6]
          msagsr = mg[7]
          msagzip = mg[8]
          display = msaglow,msaghigh,msagdir,msagsname,msageob,msagcom,msagzip,msagesn,msagsr
          story.append(display)
print story

Here is the snipped of the same search from my WxPython App:

story = []
results=[]
fields = ['LOW','HIGH','DIR','STREET NAME','EOB','COMMUNITY', 'ZIP','ESN','SRNUM']
story.append(fields)
self.SBar.SetStatusText(str(selectedfield+" "+searchvalue))
with arcpy.da.SearchCursor(fc3,[dir,sname,low,high,com,eob,esn,sr,zip,desc,rem],selectedfield+" LIKE '%"+str(searchvalue)+"%'",sql_clause=(None,'ORDER BY '+sname+' ASC, '+low+' ')) as cursor5:
     for mg in cursor5:
          msagdir = mg[0]
          msagsname = mg[1]
          msaglow = mg[2]
          msaghigh = mg[3]
          msagcom = mg[4]
          msageob = mg[5]
          msagesn = mg[6]
          msagsr = mg[7]
          msagzip = mg[8]
          display = msaglow,msaghigh,msagdir,msagsname,msageob,msagcom,msagzip,msagesn,msagsr
          story.append(display)
print story‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
18 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Chris /blogs/dan_patterson/2016/08/14/script-formatting 

Currently it is kind of hard to read

ChrisStanley
New Contributor II

OK, see if that's better, I didn't realize it would put it in there that way.....thanks


Chris

0 Kudos
ChrisStanley
New Contributor II

Also, if I need to post the whole thing I can, I was just mainly wanting to make sure I wasn't missing something and couldn't figure out why 61 will work and 55 will not.  It's very weird.


Thanks

Chris

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

At this stage, I would throw a print statement, capturing the query to make sure it is properly formatted in both cases

0 Kudos
ChrisStanley
New Contributor II

I set the query to print in my status bar and both look the same:

I have a status bar entry set before the script runs since something is causing it to fail and then one at the end just before the script finishes and opens a PDF.

This one comes up when I put in a value of 55 and the script stops just after this during the cursor.

This one works and comes up at the end of the script and then opens the PDF with the search results.  The  - END Print part is just extra string I put in to show when it printed.

They both look the same to me except for the numbers being searched.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Well, the field names look the same, the LIKE query has single quotes around them, you have the %s around the numbers.... and assuming that 55 does exist, I have no clue.  Try %5% does that bring up 55? are any other numbers experience this bizarre behaviour?

0 Kudos
ChrisStanley
New Contributor II

The %5% doesn't work and even though I get results with %62%, %6% doesn't work either.  If I do a search with any value where there aren't going to be any results the script always finishes and opens a PDF with 0 records so I know something is breaking the query but since it's in a form it's hard to troubleshoot and haven't been able to figure out anything where I can capture any errors or anything.

I have another dataset that works just fine and even just put in a record with a 355 to be able to search and it works fine as well.  I just wanted to make sure I wasn't missing anything or wasn't aware of some kind of limitation with LIKE or something.  I'm going to check the table to make sure there's nothing weird going on there since everything works in my control dataset.

Thanks


Chris

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

To state the obvious and to just ensure you are querying strings... 6 isn't the same as "6" ... first is a number, second is a string and LIKE doesn't work with numbers

0 Kudos
ChrisStanley
New Contributor II

Yes, I am using strings for my queries.

Cs

0 Kudos