topic Re: SelectLayerByAttribute_management issue, need help in Python Questions

SelectLayerByAttribute_management issue, need help

JohnRitsko — Sun, 12 Dec 2021 06:30:23 GMT

Trying to work my way through a PGDB and update the field PROJECT_STATUS depending on data in other fields. My problem seems to be in the query portion of theelectLayerByAttribute. I've tried to many iterations of this. Eventually I wanted to update this data directly in SDE on our SQL Server but I think I can figure that out once I get this. Thank you..

BELOW IS A WORKING SCRIPT. Thank you all.

#### updated script
# Import arcpy module
import arcpy, time, sys
from arcpy import env

env.overwriteOutput = True
env.workspace = "Database Connections//GISSDE.RTC.sde"
arcpy.MakeFeatureLayer_management("RTC.GISSDE.MPO_CMWG_L_TEST","cmwg_lyr")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"DESIGN\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_NTP] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CONSTRUCTION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_SUBSTANTIAL_COMPLETION] <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "RTC_PROJECT_CLOSED <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CLOSED\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[PROJECT_STATUS] IS NULL OR [PROJECT_STATUS] = ''")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"PLANNED\"", "VB", "")

Re: SelectLayerByAttribute_management issue, need help

WesMiller — Thu, 28 Jan 2016 00:07:53 GMT

You need to use Make Feature Layer—Help | ArcGIS for Desktop before you can select by attribute.

Re: SelectLayerByAttribute_management issue, need help

DarrenWiens2 — Thu, 28 Jan 2016 00:13:46 GMT

Check the first example in the SelectLayerByAttribute help page, which uses MakeFeatureLayer, as Wes says, and the correct syntax for PGDB feature classes (brackets, not quotes).

Re: SelectLayerByAttribute_management issue, need help

WesMiller — Thu, 28 Jan 2016 00:18:28 GMT

You can also do your selection with Make Feature Layer

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 00:30:50 GMT

Thanks Darren, if I'm trying to do this in SDE do I utilize quotes then?

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 00:34:10 GMT

Sorry, but I'm still a bit lost here with this function. I'm wanting to do these in order as some will overwrite ones before. Am I safe in saying that the makefeaturelayer will simply just create a singular new feature layer? I only want to update records in this feature class.

Re: SelectLayerByAttribute_management issue, need help

DarrenWiens2 — Thu, 28 Jan 2016 00:38:17 GMT

I'm not sure. You can look into it here and here.

Luckily, no matter what you use, you can get it right by using AddFieldDelimiters().

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Sun, 12 Dec 2021 06:30:26 GMT

import arcpy
from arcpy import env
env.overwriteOutput = True
env.workspace = r"C://test//CMWGp.mdb"
arcpy.MakeFeatureLayer_management("C://test//CMWGp.mdb//CMWG_L_TEST","cmwg_lyr")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] ='1'")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"DESIGN\"", "VB", "")

Here is what I tried and no luck.

Traceback (most recent call last):

File "C:\Users\ritskoj\Desktop\cmwgUpdates.py", line 19, in <module>

arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] ='1'")

File "C:\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute

raise e

ExecuteError: ERROR 000358: Invalid expression

Failed to execute (SelectLayerByAttribute).

Re: SelectLayerByAttribute_management issue, need help

WesMiller — Thu, 28 Jan 2016 00:42:18 GMT

It is the same as adding a layer to arcmap. You are still updating the feature class,but it will allow you to select by attributes or select by location as a feature layer not as a feature class.

Re: SelectLayerByAttribute_management issue, need help

DarrenWiens2 — Thu, 28 Jan 2016 00:46:15 GMT

What is the field type for [ACTUAL_DESIGN_START?

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 00:49:38 GMT

Short Integer

The others are Date and Text.

Re: SelectLayerByAttribute_management issue, need help

MichelleTotman — Thu, 28 Jan 2016 01:20:42 GMT

Hi John,

Have you considered using if/elif/else statements in the CalculateField_management code block instead of the SelectLayerByAttribute_management function?

Here is an example:

arcpy.CalculateField_management(layer, "Project_STATUS", expression="update(!ACTUAL_DESIGN_START!, !ACTUAL_NTP!)", "PYTHON", """def update(design, NTP):

if design == 1:

return "Design"

elif NTP == 1:

return "Construction"

else:

pass

""")

Re: SelectLayerByAttribute_management issue, need help

DarrenWiens2 — Thu, 28 Jan 2016 01:50:39 GMT

Remove the quotes around number values.

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 14:44:46 GMT

You sir have fixed my issue. Thank you. Both you and Wes Miller have been very helpful.

Now I do have one other questions. How do I deal with a date fields?

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 14:46:09 GMT

Michelle,

Yeah, I wanted to deal with this in and if elif situation but couldn't get my expressions working right. Now that I've got things rolling and once I figure out how to query a date I'll be set.

On that note, if you know how to ask for the current date and check against that I would be very grateful.

Thank you all for the help.

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Sun, 12 Dec 2021 06:30:29 GMT

Things are progressing well. The main answer to my question has been answered but I'm having issues with this last bit in regards to the date. I can't seem to query against the data, Invalid Expression. Any thoughts?

arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_SUBSTANTIAL_COMPLETION] <= CURRENT_DATE")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")

Re: SelectLayerByAttribute_management issue, need help

JamesCrandall — Thu, 28 Jan 2016 16:09:56 GMT

What is CURRENT_DATE?

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Sun, 12 Dec 2021 06:30:31 GMT

CURRENT_DATE is me trying to get the current date to compare against. I don't yet have a way of getting the Current Date in there. I'm now working on updating my data in SDE. The field that I'm trying to create the query against is a DATE field so I need to compare todays date against that field. Trying to figure out how to get todays date in python to match what is in SDE in SQL Server.

Note: in SQL Server we store the dates that I require my compare against as datetime2 fields.

This is how the present value is displayed.

2016-01-28 08:31:18.568000

In SQL Server the date field (ACTUAL_SUBSTANTIAL_COMPLETION)

shows 2015-09-25 00:00:00.000

present = datetime.now()
'{:%Y-%m-%d %H:%M:%S}'.format(present)

where = "[ACTUAL_SUBSTANTIAL_COMPLETION] <= " + str(present)
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", where)
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")

Re: SelectLayerByAttribute_management issue, need help

JamesCrandall — Sun, 12 Dec 2021 06:30:34 GMT

This should get you the datetime, but you may need to go thru some testing to get it just right (trust me, dates are just loads of fun to deal with).

import datetime
current_date = datetime.datetime.now()
print current_date

CURRENT_DATE in SQL? DO you mean CURRENT_TIMESTAMP?

Re: SelectLayerByAttribute_management issue, need help

JohnRitsko — Thu, 28 Jan 2016 17:30:22 GMT

Yeah, it's driving me nuts to be honest.

The Current_DATE that I mentioned was me simply trying to figure out the current date in a format that would allow my query to work.

In SDE I have a feature class that I'm trying to access the ACTUAL_SUBSTANTIAL_COMPLETION field which is a date field. I want to compare that field against todays date and if it's <= to today then I can calculate a value into the PROJECT_STATUS field. I hope this helps get you a better understanding of what I'm doing.

When I create the query it continues to tell me invalid expression.