topic Re: create sql query with arcsde connection in Python Questions

create sql query with arcsde connection

ben_abdallahmhd — Fri, 19 Jul 2013 08:28:50 GMT

hi,
i'm trying to execute this python script to establish aan sql query and the result is

"Execute SQL Statement: select [objectid) from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200"

<type 'exceptions.Exception'> 'ascii' codec can't encode character u'\ue51c' in position 52: ordinal not in range(128)

please help

>>> try:
...    env.workspace = (r"c:\connectionfiles\con21.sde")
...    sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionfiles\con21.sde")
...    SQLStatement = ("select [objectid) from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200")
...    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."
... 
Execute SQL Statement: select [objectid) from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200

<type 'exceptions.Exception'>   'ascii' codec can't encode character u'\ue51c' in position 52: ordinal not in range(128)

>>>

Re: create sql query with arcsde connection

JakeSkinner — Fri, 19 Jul 2013 10:35:28 GMT

Hi Ben,

In your SQL statement, you have [objectid). Change this to [objectid].

Re: create sql query with arcsde connection

ben_abdallahmhd — Sun, 12 Dec 2021 08:05:11 GMT

i do it but an other error message appear:

"Runtime error <type 'exceptions.UnicodeEncodeError'>: 'ascii' codec can't encode character u'\xa0' in position 6: ordinal not in range(128)"

try:
...    env.workspace = (r"c:\connectionfiles\con21.sde")
...    SQLStatement = arcpy.GetParameterAsText("select [objectid] from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200")
...    SQLStatementList = SQLStatement.split(";")
...    print "\n"
... #For each SQL statement passedin, 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."
... 
Runtime error <type 'exceptions.UnicodeEncodeError'>: 'ascii' codec can't encode character u'\xa0' in position 6: ordinal not in range(128)

Re: create sql query with arcsde connection

JakeSkinner — Fri, 19 Jul 2013 11:31:30 GMT

Try the following query:

select objectid from P10TN_SIG.DBO.BCBATIMS where Shape.STArea() > 200

Re: create sql query with arcsde connection

ben_abdallahmhd — Fri, 19 Jul 2013 11:42:00 GMT

Try the following query:

select objectid from P10TN_SIG.DBO.BCBATIMS where Shape.STArea() > 200

hi JSkinn3
i try it but i I had the same result

 Runtime error <type 'exceptions.UnicodeEncodeError'>: 'ascii' codec can't encode character u'\xa0' in position 6: ordinal not in range(128)

Re: create sql query with arcsde connection

ben_abdallahmhd — Sun, 12 Dec 2021 08:05:14 GMT

>>> try:
...    env.workspace = (r"c:\connectionfiles\con21.sde")
...    sdeConn = arcpy.ArcSDESQLExecute(r"c:\connectionfiles\con21.sde")
...    SQLStatement = ("select [objectid) from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200")
...    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."
... 
Execute SQL Statement: select [objectid) from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] > 200

<type 'exceptions.Exception'>   'ascii' codec can't encode character u'\ue51c' in position 52: ordinal not in range(128)

>>>

hi all
i have a mistake in this script in line 33

   print Exception, ErrorDesc

should be :

print "Exception, ErrorDesc"

now the result of the script it's:

>>> try:

...     sdeConn = arcpy.ArcSDESQLExecute(r"connectionFiles\con21.sde")
...     # Get the SQL statements, separated by ; from a text string.
...     SQLStatement = ('select [VOCBATIM] from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] < 500,0')
...     SQLStatementList = SQLStatement.split(";")
...     print "\n"
...     
...     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."
... 
Exception, ErrorDesc

Re: create sql query with arcsde connection

ben_abdallahmhd — Sun, 12 Dec 2021 08:05:16 GMT

hi all
i have a mistake in this script in line 33

   print Exception, ErrorDesc

should be :

print "Exception, ErrorDesc"

now the result of the script it's:

>>> try:

...     sdeConn = arcpy.ArcSDESQLExecute(r"connectionFiles\con21.sde")
...     # Get the SQL statements, separated by ; from a text string.
...     SQLStatement = ('select [VOCBATIM] from [P10TN_SIG.DBO.BCBATIMS] where [shape.area] < 500,0')
...     SQLStatementList = SQLStatement.split(";")
...     print "\n"
...     
...     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."
... 
Exception, ErrorDesc

there is no result