Help needed with incorporating SQL query into ArcGIS python script

Question

I'm new to python and have been trying to learn how to use it within ArcGIS. I have primarily been using the free Penn State courseware available here - http://open.ems.psu.edu/courseware

I have understood most of the content, however, I am struggling to understand how the instructors have incorporated the SQL query into this script:

(The comments and course notes related to this scrip can be viewed here - https://www.e-education.psu.edu/geog485/node/136)

import arcpy

fc = arcpy.GetParameterAsText(0)
affectedField = arcpy.GetParameterAsText(1)
oldValue = arcpy.GetParameterAsText(2)
newValue = arcpy.GetParameterAsText(3)

queryString = '"' + affectedField + '" = '+ "'" + oldValue + "'"

rows = arcpy.UpdateCursor(fc, queryString)
row = rows.next()

while row:
row.setValue(affectedField, newValue)
rows.updateRow(row)
row = rows.next()

del row, rows

The above script is used to update the attribute table in ArcGIS. In line 8 an SQL query is used to identify a particular name that I want to change (I used a work related attribute table here and wanted to change the name of a city from 'Aberdeen' to 'Newcastle'). However, having very little experience with SQL I am not sure what the instructors mean or how to apply it to my own task

Could anyone please help me to understand what the SQL query in line 8 means and how to use it to complete my task?

Thanks,

Chris

Answer 1

The query string is the roughly equivalent to the WHERE clause of a SQL Statement:
column_name operator value;

In your example, the script is picking up the field name (aka column name aka attribute name) and assigning to a variable affectedField (instead of hard-coding the field name).

operator is equals (=)

The oldValue is text (so it gets wrapped in single quotes).

When you put it together: column_name operator value becomes City='Aberdeen'

If you put in a print queryString at line 9, you'll see the actual query.

Answer 2

You may simply be missing the fact that affectedField and oldValue are input variables. When the script runs you are asked to enter values for those first four variables (fc, affectedField, oldValue, newValue).

The queryString variable will be populated with those input variables. Assuming you enter a field name and value that exist in your Featureclass, the SQL query will leave you with the results of that query. Due to the way the formatting has to be applied within ArcPy, the SQL query formmating looks more complicated than it really is. If you print queryString you will see it more clearly, it is literally "affectedField" = 'oldValue'.

Try it, add this line under your queryString line:

print(queryString)

The variable queryString is set to this SQL statement and used as the "where" statement in your Update Cursor. All of the records meeting that criteria will be updated with a new value from your newValue input variable.

You may also want to review Esri's documentation on SQL Expressions and Update Cursors.