ArcSDESQLExecute

Resumen

The ArcSDESQLExecute class provides a means of executing SQL statements via an ArcSDE connection.

Debate

PrecauciónPrecaución:
  • Las tablas de ArcSDE y GDB no se deben modificar utilizando nada que no sea el software de ArcGIS. Se pueden producir daños si estas tablas del sistema se editan directamente utilizando SQL.
  • Las ediciones con SQL de datos sometidos al control de versiones solo se deben hacer a través de vistas versionadas.
  • Para las geodatabases implementadas en un sistema de administración de bases de datos relacionales (DBMS) utilizando tipos de datos y formatos de tablas del DBMS, se puede utilizar el SQL del propio DBMS para trabajar con la información almacenada en la base de datos.
  • Al acceder a la información en una geodatabase a través de SQL, las aplicaciones externas pueden acceder a los datos tabulares que administra la geodatabase. Estas aplicaciones externas pueden ser aplicaciones de bases de datos no espaciales o aplicaciones espaciales personalizadas desarrolladas en un entorno distinto de ArcObjects. Sea consciente, sin embargo, de que el acceso SQL a la geodatabase omite la funcionalidad de la geodatabase, tal como topología, redes, terrenos u otras extensiones de clase o espacio de trabajo.
  • Podría ser posible usar las entidades del DBMS como desencadenadores y los procedimientos almacenados para mantener las relaciones entre las tablas que son necesarias para ciertas funcionalidades de geodatabase. Sin embargo, si se ejecutan comandos SQL contra la base de datos sin tener en cuenta esta funcionalidad, por ejemplo si se emiten instrucciones INSERT para agregar registros a una tabla de negocios, se evitará la funcionalidad de la geodatabase y, probablemente, se dañarán las relaciones entre los datos de la geodatabase.
  • Antes de intentar obtener acceso a objetos de ArcSDE o GDB, o de modificarlos, lea toda la documentación de ArcSDE y de la geodatabase relativa a la utilización de SQL contra los objetos GDB o ArcSDE en el DBMS.

Sintaxis

ArcSDESQLExecute ({server}, {instance}, {database}, {user}, {password})
ParámetroExplicaciónTipo de datos
server

Name of the server on which the database is installed.

String
instance

The port number

String
database

Name of the database.

String
user

The user name.

String
password

The password for the user name.

String

Propiedades

PropiedadExplicaciónTipo de datos
transactionAutoCommit
(Lectura y escritura)

The autocommit interval. This can be used to force intermediate commits after a specified number of features have been modified.

Integer

Vista general del método

MétodoExplicación
commitTransaction ()

No DML statements will be committed until the CommitTransaction method is called.

NotaNota:

A commit may also occur when the connection to ArcSDE it terminated (check specific DBMS documentation to see how each DBMS deals with a disconnect while in a transaction).

execute (sql_statement)

Sends the SQL statement to the database via an ArcSDE connection. If execute is run outside of a transaction, a commit will automatically take place once the SQL DML (INSERT, UPDATE, DELETE . . .) statement has been executed.

rollbackTransaction ()

Rollback any DML operations to the previous commit.

startTransaction ()

To control when your changes are committed to the database, call the startTransaction method before calling execute. This starts a transaction and no DML statements will be committed until the commitTransaction method is called.

Métodos

commitTransaction ()
execute (sql_statement)
ParámetroExplicaciónTipo de datos
sql_statement

The SQL statement.

String
rollbackTransaction ()
startTransaction ()

Ejemplo de código

Ejecutar una lista de instrucciones SQL
 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("data\Connection to GPSERVER3.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: " + str(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."  
Actualización condicional utilizando una transacción
# WARNING - DO NOT USE ON VERSIONED TABLES OR FEATURE CLASSES.  #   DO NOT USE ON ANY ArcSDE or GDB SYSTEM TABLES. #   DOING SO MAY RESULT IN DATA CORRUPTION.  import arcpy from arcpy import env import sys  try:     # Make data path relative (not relevant unless data is moved here and paths modified)     #     env.workspace = sys.path[0]      #Column name:value that should be in the record.     #     SQLvalues = {"STREET_NAM":"'EUREKA'"}      #Value that is incorrect if found in the above column.     #     badVal = "'EREKA'"      #List of tables to look in for the bad value.     #     tableList = ["streetaddresses_blkA","streetaddresses_blkB", "streetaddresses_blkC"]          # 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("data\Connection to GPSERVER3.sde")      for tbl in tableList:         print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"         for col, val in SQLvalues.items():             print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"             #Check for the incorrect value in the column for the specific rows. If the table contains the              #incorrect value, correct it using the update SQL statement.             #             print "Analyzing table " + tbl + " for bad data: Column:" + col + " Value: " + badVal             try:                 sql = "select OBJECTID," + col + " from " + tbl + " where " + col + " = " + badVal                 print "Attempt to execute SQL Statement: " + sql                 sdeReturn = sdeConn.execute(sql)             except Exception, ErrorDesc:                 print ErrorDesc                 sdeReturn = False             if isinstance(sdeReturn, list):                 if len(sdeReturn) > 0:                     print "Identified " + str(len(sdeReturn)) + " rows with incorrect data. Starting transaction for update."                     # Start the transaction                     #                     sdeConn.startTransaction()                     print "Transaction started....."                     # Perform the update                     #                     try:                         sql = "update " + tbl + " set " + col + "=" + str(val) + " where " + col + " = " + badVal                         print "Changing bad value: " + badVal + " to the good value: " + val + " using update statement:\n " + sql                         sdeReturn = sdeConn.execute(sql)                     except Exception, ErrorDesc:                         print ErrorDesc                         sdeReturn = False                      # If the update completed sucessfully, commit the changes.  If not, rollback.                     #                     if sdeReturn == True:                         print "Update statement: \n" + sql + " ran successfully."                         # Commit the changes                         #                         sdeConn.commitTransaction()                         print "Commited Transaction"                          # List the changes.                         #                         try:                             print "Displaying updated rows for visual inspection."                             sql = "select OBJECTID," + col + " from " + tbl + " where " + col + " = " + val                             print "Executing SQL Statement: \n" + sql                             sdeReturn = sdeConn.execute(sql)                         except Exception, ErrorDesc:                             print ErrorDesc                             sdeReturn = False                         if isinstance(sdeReturn, list):                             print len(sdeReturn), "rows"                             for row in sdeReturn:                                 print row                             print "+++++++++++++++++++++++++++++++++++++++++++++\n"                         else:                             if sdeReturn == True:                                 print "SQL statement: \n" + sql + "\nran successfully."                                 print "+++++++++++++++++++++++++++++++++++++++++++++\n"                             else:                                 print "SQL statement: \n" + sql + "\nFAILED."                                 print "+++++++++++++++++++++++++++++++++++++++++++++\n"                          print "+++++++++++++++++++++++++++++++++++++++++++++\n"                     else:                         print "SQL statement: \n" + sql + "\nFAILED.  Rolling back all changes."                         # Rollback changes                         #                         sdeConn.rollbackTransaction()                         print "Rolled back any changes."                         print "+++++++++++++++++++++++++++++++++++++++++++++\n"             else:                 print "No records required updating."                  # Disconnect and exit     del sdeConn     print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"  except Exception, ErrorDesc:     print Exception, ErrorDesc except:     print "Problem executing SQL."

Temas relacionados


7/10/2012