Pass row.getValue(field.name) into a SQL expression

189
2
09-17-2011 04:42 PM
New Contributor
What I want to do is:
1.  Create folders based on "Wall_Num" value in "tbl_Wall_Numbers" row by row
2.  As each row is selected, use "Wall_Num" value to plug into an SQL expression that select records from a different table "Results"
3.  Do something other stuff with the records in "Results"


I am able to get the part of creating folders fine, but when I try to pass row.getValue(field.name) into a SQL expression, it just says I have invalid expression.  I have tried many different ways to modify it but it doesn't seem to want to work.  I am working in ArcGIS 10 and using a file geodatabase. Can this be done?  I also have tried to set up another SearchCursor but run into the same invalid SQL expression problem.

Thanks!

# Import arcpy module
import arcpy, os

# Import all of arcpy
from arcpy import env

# Local variables:
fc = "Results"
WallNum = "tbl_Wall_Numbers"

# Change dir
os.chdir("C:\Project")

# Set the workspace environment
env.workspace = "C:\project.gdb"

# Use the unique list of wall numbers
rows = arcpy.SearchCursor(WallNum)
fieldList = arcpy.ListFields(WallNum,"Wall_Num")

for row in rows:
    for field in fieldList:
        # Create the wall folders
        os.makedirs("Wall" + str(row.getValue(field.name)))
       
        # Query for records in the wall
        arcpy.MakeFeatureLayer_management(fc, "wall_select")
        arcpy.SelectLayerByAttribute_management ("wall_select", "NEW_SELECTION", "[Wall_Num] = " + row.getValue(field.name))
Tags (2)
Reply
0 Kudos
2 Replies
New Contributor III
according to http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s500000033000000.htm when query any file based data sources (even file geodatabase) you must enclose the field in double quotes. so, your query shoud be like:

arcpy.SelectLayerByAttribute_management ("wall_select", "NEW_SELECTION", "\"Wall_Num\" = " + row.getValue(field.name)) 


then chek the value you pass to the query. sometimes concatenating strings and values got from variables produces some kind of errors. Use
str(row.getValue(field.name)) 


and check the type of the data, string must be enclosed in single quotes

ciao,
AC
Reply
0 Kudos
MVP Esteemed Contributor
I personally like to use string substitution. This has the added benefit of being easier to read.

sqlExpr = "\"Wall_num\" = %s" % row.getValue(field.name)
arcpy.SelectLayerByAttribute_management ("wall_select", "NEW_SELECTION",strExpr)


For string fields you can put the single quotes in like this:

sqlExpr = "\Wall_num\" = \'%s\'" % row.getValue(field.name)
Reply
0 Kudos