1

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

  • Some people prefer this to string concatenation, queryString = "'{0}' = '{1}'".format(affectedField, oldValue) – klewis May 21 '14 at 16:17
0

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.

  • Thanks for your help. I tried inputting print(queryString) and that clarified a few things. However, I think to really get to grips with editing the attribute table with python I'm going to have learn a little about SQL. – Geord359 May 22 '14 at 10:02
  • @Geord359 Good luck. If you are satisfied with either of the answers here, you may want to select one of them as an accepted answer. – amasephy May 22 '14 at 17:49
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.