SelectLayerByAttribute_management issue, need help

3165
25
Jump to solution
01-27-2016 03:47 PM
JohnRitsko
New Contributor III

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", "")

Reply
0 Kudos
1 Solution

Accepted Solutions
WesMiller
Regular Contributor III

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

View solution in original post

25 Replies
WesMiller
Regular Contributor III

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

View solution in original post

BlakeTerhune
MVP Regular Contributor

As a side note, I noticed that doing SelectLayerByAttribute on a Feature Layer from SDE is still "connected" to SDE. In other words, SelectLayerByAttribute will hit your database! I found that copying features (any means) to in_memory workspace truly works independently of the database. Although I haven't tested it, MakeTableView probably has the same behavior.

Reply
0 Kudos
curtvprice
MVP Esteemed Contributor

Yes, I cringe when I see things in the help about layer data as "in-memory" -- a layer is simply an object pointing to the data, what's held in memory is just the metadata about it (schema, selection criteria, etc).  Copying the data out is sometimes useful, but even then creating another layer to point to it has performance benefits. (When you pass a layer to a tool instead of a dataset, the tool doesn't have to check that the data is available and what the fields are - that info is pre-loaded into the layer object already.)

DarrenWiens2
MVP Honored Contributor

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).

JohnRitsko
New Contributor III

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

Reply
0 Kudos
DarrenWiens2
MVP Honored Contributor

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().

WesMiller
Regular Contributor III

You can also do your selection with Make Feature Layer

JohnRitsko
New Contributor III

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.

Reply
0 Kudos
WesMiller
Regular Contributor III

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.

Reply
0 Kudos