nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Port Old Python to python 3 expression help

RaymondSeth_III1

I am trying to port this code to python 3 and am having a terrible time trying to recreate this expression. I am really new to python but more specifically these expressions. the varible dateStart and dateEnd are int as well as Num in the table being a LONG

expression = "mid(trim(str([Num])), 5, 4) not between '" + dateStart + "' and '" + dateEnd + "'"

arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

Find more posts tagged with

Accepted answers

All comments

RaymondSeth_III1

I am going to work on this approach I have been out of the office a couple days I will report back thanks for the for the recommendations.

rzufelt

Not sure how it was done in ArcMap, but don't really think this is possible.

from documentation:

Here is a basic form of a SQL expression WHERE clause:
<Field_name> <Operator> <Value or String>

So, not sure how having a value for the <Field_name> would work as the expression evaluates row by row comaring a field value to some expression.

However, if you were to make a new column in the data, say Num2 and calculate that to:

Num2 = int(str(Num)[4:])

That will populate the Num2 field with the last 4 digits of the Num value:

>>> Num = 20221007
>>> Num2 = int(str(Num)[4:])
>>> Num2
1007

You would then have a "Field" with the value you want to compare with the expression. Something like:

expression = f"Num2 BETWEEN {startDate} And {endDate}"
arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

This is working for me.

Suspect you could use cursors to append the matching OID's to a list and select by that.

Interesting to see if you find a solution as I'm not aware of how to use a variable for field input.

RaymondSeth_III1

It's old python code that works as is in ArcMap 10 as a script tool and I am trying to port it to work in pro. I have tried both running it as a script tool and working with just those lines of code in a notebook within the pro-environment for troubleshooting and I am getting the same errors doing it either way. The Num is a LONG datatype attribute in a table within a feature class that will have a number like 20221007 that represents a date, the start and end date are basically just 4 digit numbers from user input that would be in the format of 1007 basically an integer that contains the month and day. So the first part of that expression is trying to strip out just the month and day (last 4 digits) so it can be compared to see if it falls outside the range given by the user.

rzufelt

So you are trying to convert old VBA code to python code to genereate an SQL expression to feed into the select by attributes tool.

You say they are int/long fields and not date.

Do the date fields have just day, just month, days since 1/1/1901, etc. since they are int, they must be a number and can't resemble "10/7/2022". Also, what is the number field? How many digits, etc.

Would be easier to troubleshoot if we have examples of what the data looks like rather than trying to guess what you are after.

Would also help if we knew "where" you are trying to do this. Script tool, stand alone python, python in Pro, Notebook, etc.

RaymondSeth_III1

how would I write that if Num is a table attribute?

RaymondSeth_III1

This was the original code.

expression = "mid(trim(str([Num])), 5, 4) not between '" + dateStart + "' and '" + dateEnd + "'"

arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

bixb0012

If you are using ArcGIS Pro, I suggest you use literal strings or f-strings:

expression = f"{str(Num)[4:7]} not between {dateStart} and {dateEnd}"

bixb0012

When I try just building your expression I get an error before I even get to the arcpy tool: (The specific values I used don't matter for test building expression, just the data types matter, and I had to change !Num! to just Num for testing)

>>> Num = 123456
>>> dateStart = 1
>>> dateEnd = 2
>>> expression = "str(Num)[4:7] not between '" + dateStart + "' and '" + dateEnd + "'"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: can only concatenate str (not "int") to str
>>>

Are you sure you are pasting your exact code?

bixb0012

Python slicing is start to stop, not start through stop, so [5:8] returns only 3 characters and not 4 characters.

RaymondSeth_III1

So, I have changed it to this and still get an invalid expression error.

expression = "str(!Num!)[4:7] not between '" + dateStart + "' and '" + dateEnd + "'"

arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

RaymondSeth_III1

yes sorry that was a copy and paste error as it appears a couple of times in the code but it matches in the code.

Kara_Shindle

Is the expression variable correct? You set the expression with the variable named "expression1" but the function is taking a variable named "expression"

RaymondSeth_III1

That may help with part of it, but I am not sure I am formatting the entire expression correctly as I keep getting invalid expression error. The quotes and single quotes are really confusing.

LeanneRodriguez

So mid(string, 5, 4) meaning give me the substring beginning at character 5 and 4 chars long, right?

In python it would be:

str(!FieldName!)[5:8]

or.. give me a string starting at position 5 and ending at position 8
so it returns characters at positions 5,6,7,8

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.