Script tool fails in Model Builder

Question

Original User: SVisovsky

I'm having a heck of a problem getting a script tool to work in model builder.  The script works just fine on its own when run from IDLE, but it invariably throws the same error when it runs as part of my model.  I'm using the pyodbc module to talk to an SQL database and run a pair of stored procedures there as part of my model.  The stored procedures populate the FACILITYID field in two layers; the rest of the model puts the FACILITIYID from PlantingSpace into the FACILITYID_PS field in TreePoint based on spatial intersection.   I took out the assorted connection details, but I double checked to make sure they all work before making the changes here.

The most annoying thing is that it does work sometimes.  I'm not certain, but I think that's the case the first time it runs after opening ArcCatalog/ArcMap.  That said, the error I'm getting is:
AttributeError: 'module' object has no attribute 'lowercase'

I suspect either that I set up the script tool incorrectly, Model Builder doesn't like python in general or pyodbc in particular, or that Model Builder doesn't like using scripts as preconditions.  I've also read something about importing multiple python modules causing problems, but I'm new enough to scripting that I have no idea what any of that discussion means.  I'm running ArcGIS 10.0, SP2 on Win7 64bit.  The server is running MS SQL Server 2005.  Any advice would be helpful.

Here's the model:
[ATTACH=CONFIG]25611[/ATTACH]

and the way I set up the script tool:
[ATTACH=CONFIG]25612[/ATTACH][ATTACH=CONFIG]25612[/ATTACH]

and here's my code:
import arcpy
import pyodbc

#variables yay
Tcount = 0;
Pcount = 0;
Prow = 0;
Trow = 0;
OutputSuccess = 'false';
arcpy.SetParameterAsText(0, 'false')

#Establish DB connection

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=REDACTED;DATABASE=REDACTED;UID=REDACTED;PWD=REDACTED;autocommit=True')

#Create cursors for Trees (Tcursor, Trows)(Pcursor, Prows)
#then selects null values and blank strings in FACILITYID

Tcursor = cnxn.cursor()
Tcursor.execute("SELECT (OBJECTID) FROM *TREEPOINT* WHERE FACILITYID IS NULL OR FACILITYID = ''")
Trows = Tcursor.fetchall()

#Loop one executes stored procedure (takes OBJECTID) for every row with a null FACILITYID in TreePoint
#Prints the total number of records or says there are no null IDs
if len(Trows) > 0:
    for Trow in Trows:
        ExecuteString = "exec *STORED PROCEDURE* %i" %int(Trow.OBJECTID)
        Tcursor.execute(ExecuteString) 
        Tcount+= 1
    arcpy.AddMessage('Planting Space count: ' + str(Tcount))
else:
    arcpy.AddMessage('No NULL FacilityIDs found in TreePoint')
Tcursor.close()

#Create cursors for PlantingSpace (Pcursor, Prows)
#then selects null values and blank strings in FACILITYID

Pcursor = cnxn.cursor()
Pcursor.execute("SELECT (OBJECTID) FROM *PLANTINGSPACE* WHERE FACILITYID IS NULL OR FACILITYID = ''")
Prows = Pcursor.fetchall()

#Loop two executes stored procedure (takes OBJECTID) for every row with a null FACILITYID in PlantingSpace
#Prints the total number of records or says there are no null IDs
if len(Prows) > 0:
    for Prow in Prows:
        ExecuteString = "exec *STOREDPROCEDURE* %i" %int(Prow.OBJECTID)
        Pcursor.execute(ExecuteString) 
        Pcount+= 1
    arcpy.AddMessage('Planting Space count: ' + str(Pcount))
else:
    arcpy.AddMessage('No NULL FacilityIDs in Planting Spaces')
Pcursor.close()
    
#Create boolean output value for use in model
#Should be 1/true if script ran, 0/false if it didn't.
if (Tcount>0 or Pcount>0):
        arcpy.SetParameterAsText(0, 'true')
else:
    arcpy.SetParameterAsText(0, 'false')

#commit changes to DB and close connection
cnxn.commit()
cnxn.close()

ScriptToolScreenshot.JPG

ModelScreenshot.jpg

23541c32-fc0e-49e8-8b90-548c7bf27de8.png

TonghuiMing_0-1750407913945.png

Anonymous User · Answer

Original User: StacyRendall

OK, we're getting somewhere! Glad that the changes worked.

To fix the last bit, we will need to partially run the model. The model is made up of inputs, processes and outputs.

To partially run the model you need to provide actual inputs, do this by double clicking on each of the model inputs then pointing them to something, either some files suitable for testing or the real data. This will cause everything to become coloured, rather than black and white. Then you need to run each process individually by right clicking it and selecting Run, once it has run successfully it will get a shadow underneath it, it will also generate the inputs for the next process. Go through until all you have left is the Calculate Field tool. Run that, it will bring up a running dialog, and give you an error when it breaks.

Hopefully the error will help you solve the problem, or you can post the error and your Calculate Field code here...

StephenVisovsky · Answer

Stacy,

Thanks for the reply!  Besides the connection details, that's exactly the code that's failing.  You're right about the lowercase() thing, it's incredibly perplexing.  I wanted to attach a screenshot of the error itself, but after several days of the model always failing at the script, it succeeded several times in a row before hanging on the calculate field at the end (resulting in killing the program through the task manager after an hour of a progress bar at 99% when the preceding 98% took less than a minute).  I'm sure I didn't change anything when that happened, but it's possible .  Still, having the whole thing work only when the planets achieve the proper alignment is unacceptable and I need to fix it.

I just tried running the code you posted and it worked both on its own (through IDLE) and as part of the model.  The model died with the calculate field at 99% again, but that's (probably) not a python issue.  I'm going to have to run it a few more times to make sure the python-related error doesn't crop up again and to see if I can decrease the number of records it has to process.

Anonymous User · Answer

Original User: StacyRendall

Hi Stephen, is the code you shared the exact code that is failing? The error you mentioned (AttributeError: 'module' object has no attribute 'lowercase') would require that the lowercase() method (used to make a string lowercase, i.e. change 'TITLE' to 'title') is applied to something that is not a string, like a function (i.e. arcpy.SetParameterAsText(0, 'false).lowercase()). However, I cannot find lowercase in your code.

Also, the error message should indicate a line number, which is invauable in finding the source of the problem, and can also indicate if the error is happening in one of the imported modules, or in your script itself - please include the full error message if the suggestions below do not help.

I have never had problems with multiple Python imports, and seriously doubt it is your issue. Comaptibility with pyobdc is a possibility; try Googling 'arcpy pyobdc' and see if anything turns up - you might find other people having issues, or that other scripts combining the two exist and run fine...

Please try running the code below and let me know how you get on. Here is what I changed:* arcpy.SetParameterAsText(0, 'false') to arcpy.SetParameter(0, False) - that is, use a Python False. The text false might be causing the problem, as Arc tries to convert it to a Boolean...? If you have provided the actual code that is failing, this is the only possibility I can see, other than potential compatibility issues.
* Removed the arcpy.SetParameter call at the start (I don't think this is causing a problem, but it might...). Your intention with that is: if it fails; don't return a positive. There are other ways to do this that are more reliable (i.e. Try...Except), let me know if you want more info.
* Removed the semicolons, I don't think they do anything.
* Removed the OutputSuccess = 'false'; statement, it is unnecessary.

import arcpy
import pyodbc

#variables yay
Tcount = 0
Pcount = 0
Prow = 0
Trow = 0

#Establish DB connection
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=REDACTED;DATABASE=REDACTED;UID=REDACTED;PWD=REDACTED;autocommit=True')
    
#Create cursors for Trees (Tcursor, Trows)(Pcursor, Prows)
#then selects null values and blank strings in FACILITYID

Tcursor = cnxn.cursor()
Tcursor.execute("SELECT (OBJECTID) FROM *TREEPOINT* WHERE FACILITYID IS NULL OR FACILITYID = ''")
Trows = Tcursor.fetchall()

#Loop one executes stored procedure (takes OBJECTID) for every row with a null FACILITYID in TreePoint
#Prints the total number of records or says there are no null IDs
if len(Trows) > 0:
    for Trow in Trows:
        ExecuteString = "exec *STORED PROCEDURE* %i" %int(Trow.OBJECTID)
        Tcursor.execute(ExecuteString) 
        Tcount+= 1
    arcpy.AddMessage('Planting Space count: ' + str(Tcount))
else:
    arcpy.AddMessage('No NULL FacilityIDs found in TreePoint')
Tcursor.close()

#Create cursors for PlantingSpace (Pcursor, Prows)
#then selects null values and blank strings in FACILITYID

Pcursor = cnxn.cursor()
Pcursor.execute("SELECT (OBJECTID) FROM *PLANTINGSPACE* WHERE FACILITYID IS NULL OR FACILITYID = ''")
Prows = Pcursor.fetchall()

#Loop two executes stored procedure (takes OBJECTID) for every row with a null FACILITYID in PlantingSpace
#Prints the total number of records or says there are no null IDs
if len(Prows) > 0:
    for Prow in Prows:
        ExecuteString = "exec *STOREDPROCEDURE* %i" %int(Prow.OBJECTID)
        Pcursor.execute(ExecuteString) 
        Pcount+= 1
    arcpy.AddMessage('Planting Space count: ' + str(Pcount))
else:
    arcpy.AddMessage('No NULL FacilityIDs in Planting Spaces')
Pcursor.close()

#commit changes to DB and close connection
cnxn.commit()
cnxn.close()

#Create boolean output value for use in model
#Should be 1/true if script ran, 0/false if it didn't.
if (Tcount>0 or Pcount>0):
    arcpy.SetParameter(0, True)
else:
    arcpy.SetParameter(0, False)