1

I'm just starting out with ArcPy and ArcSDESQLExecute.

I'm trying to use the 'Execute a list of SQL statements' ArcPy script from http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002z00000021000000.htm

import arcpy
from arcpy import env
import sys

try:
# Make data path relative
#
env.workspace = sys.path[0]

# Two ways to create the object, which also creates the connection to ArcSDE.
#   Using the first method, pass a set of strings containing the connection properties:
#   <serverName>,<portNumber>,<version>,<userName>,<password>
#   sdeConn = arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
#   Using the second method pass the path to a valid ArcSDE connection file
#
sdeConn = arcpy.ArcSDESQLExecute(r'Database Connections\GIS_Connection.sde')

# Get the SQL statements, separated by ; from a text string.
#
SQLStatement = arcpy.GetParameterAsText(0)
SQLStatementList = SQLStatement.split(";")

print "+++++++++++++++++++++++++++++++++++++++++++++\n"

# For each SQL statement passed in, execute it.
#
for sql in SQLStatementList:
    print "Execute SQL Statement: " + sql
    try:
        # Pass the SQL statement to the database.
        #
        sdeReturn = sdeConn.execute(sql)
    except Exception, ErrorDesc:
        print ErrorDesc
        sdeReturn = False

    # If the return value is a list (a list of lists), display each list as a row from the 
    #   table being queried.
    if isinstance(sdeReturn, list):
        print "Number of rows returned by query: " + len(sdeReturn), "rows"
        for row in sdeReturn:
            print row
        print "+++++++++++++++++++++++++++++++++++++++++++++\n"
    else:
        # If the return value was not a list, the statement was most likely a DDL statment. 
        #   Check its status.
        if sdeReturn == True:
            print "SQL statement: " + sql + " ran sucessfully."
            print "+++++++++++++++++++++++++++++++++++++++++++++\n"
        else:
            print "SQL statement: " + sql + " FAILED."
            print "+++++++++++++++++++++++++++++++++++++++++++++\n"

except Exception, ErrorDesc:
    print Exception, ErrorDesc
except:
    print "Problem executing SQL."

I'm getting this error: " 'ascii' codec can't encode character u'\udf9c' in position 52: ordinal not in range(128)"

What have I done wrong? I don't know if it's a problem with the ascii formatting in the script, or if the SQL statement needs to be put in the script somewhere, or if it's something else?

ArcGIS/SDE 10.3.1

closed as off-topic by PolyGeo Apr 21 '17 at 4:50

  • This question does not appear to be about geographic information systems (GIS) within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I'm voting to close this question as off-topic because the asker has requested that be done via a flag – PolyGeo Apr 21 '17 at 4:50
1

Try adding sql=unicode(sql) just after your for loop. If you are sure that your sql list entries are correct, this should overcome this issue, at least.


UPDATE AFTER OP'S COMMENT

In fact your code looks like a Toolbox "Script" as detailed in this help link. To keep long story short, these following two statements in your code:

SQLStatement = arcpy.GetParameterAsText(0) and SQLStatementList = SQLStatement.split(";")

expects a string as a parameter to this script which then to be splitted by using ";" delimiter. You have two options, all of which do exactly the same thing. Assume you have two sql statements as

select OBJECTID,STREET_NAM from streetaddresses_blkA where STREET_NAM = 'EREKA' and

select OBJECTID,STREET_NAM from streetaddresses_blkB where STREET_NAM = 'EREKA'.

Option 1. Create a script as explained in this link by setting one string parameter and enter select OBJECTID,STREET_NAM from streetaddresses_blkA where STREET_NAM = 'EREKA';select OBJECTID,STREET_NAM from streetaddresses_blkB where STREET_NAM = 'EREKA'

Option 2. Change SQLStatement = arcpy.GetParameterAsText(0) as SQLStatement = """select OBJECTID,STREET_NAM from streetaddresses_blkA where STREET_NAM = 'EREKA';select OBJECTID,STREET_NAM from streetaddresses_blkB where STREET_NAM = 'EREKA'""" or better create a python list directly for SQLStatementList. This option is for running your script from Python interpreter within ArcMap/Catalog.

When it comes to the error that you received, it is a well-know source of frustration for python beginners and may require you to spend some time to find ways to avoid it. Lastly it is hard to tell without knowing which character throws this exception and the context of sql that you are going to apply but I have a feeling that your issue will persist even if you adopt any of my suggestions.

  • @Wilson456 I think that might be a separate question that you need to ask (or search for solutions here) – Midavalo Aug 10 '16 at 8:34
  • For what it's worth, I used a script from a slightly different Esri page: desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-classes/…. I don't know why, but the script/page that I was originally using kept throwing errors. I don't know enough about python to determine why one script worked, and the other didn't, even though they appear to be nearly identical. – Wilson Aug 10 '16 at 16:40

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