topic Pass row.getValue(field.name) into a SQL expression in Python Questions

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

dreamfool — Sat, 17 Sep 2011 23:42:57 GMT

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

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

AlessandroCinnirella — Sun, 18 Sep 2011 20:14:40 GMT

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

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

curtvprice — Sat, 11 Dec 2021 09:57:39 GMT

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)