Ejecutar SQL utilizando una conexión de ArcSDE
A veces, al trabajar con tablas no sometidas al control de versiones, puede parecer más fácil consultar una tabla de una base de datos utilizando el lenguaje estructurado de consulta (SQL) en lugar de utilizar una de las herramientas de geoprocesamiento. El objeto ArcSDESQLExecute admite la ejecución de la mayoría de las instrucciones SQL y devuelve al usuario los resultados de esas instrucciones. El objeto devuelve una lista de listas en el caso de que la instrucción devuelva filas de una tabla; para las instrucciones que no devuelven filas, devuelve una indicación del éxito o error de la instrucción (True para éxito, None para error). Las instrucciones que devuelven un valor único de una fila única devolverán el valor en un tipo adecuado (cadena de caracteres, flotante)
Precaució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, 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.
|
Propiedad | |
|---|---|
|
transactionAutoCommit |
Intervalo de confirmación automática. Se puede utilizar para forzar confirmaciones intermedias una vez modificado un número determinado de entidades. |
|
Métodos | |
|---|---|
|
commitTransaction() |
No se confirmará ninguna instrucción DML hasta que se llame al método commitTransaction.  Nota:También se puede producir una confirmación cuando finalice la conexión con ArcSDE (consulte la documentación específica del DBMS para ver cómo trata cada DBMS las desconexiones durante una transacción). |
|
execute(sql_statement) |
Envía la instrucción SQL a la base de datos a través de una conexión de ArcSDE. Si se ejecuta execute fuera de una transacción, tendrá lugar automáticamente una confirmación cuando se ejecute la instrucción SQL (INSERT, UPDATE, DELETE). |
|
rollbackTransaction() |
Deshacer cualquier operación DML hasta la confirmación anterior. |
|
startTransaction() |
Para controlar cuándo se confirman los cambios en la base de datos, llame al método startTransaction antes de llamar a execute. Así se inicia una transacción y no se confirma ninguna instrucción DML hasta que se llama al método commitTransaction. |
El método execute envía la instrucción SQL a la base de datos a través de una conexión de ArcSDE. Si se ejecuta execute fuera de una transacción, tendrá lugar automáticamente una confirmación cuando se ejecute la instrucción SQL (INSERT, UPDATE, DELETE).
ArcSDESQLExecute admite el modelo de transacción de ArcSDE. Las transacciones son una propiedad de una conexión ArcSDE, y enlazan operaciones de modo que se acepte o se rechace un conjunto completo de cambios. Por ejemplo, si se actualiza un conjunto de parcelas en un orden determinado, puede utilizar una transacción para definir el principio y el fin de los cambios, para que todos los cambios se envíen juntos. Si un conjunto de cambios no se puede insertar correctamente, se rechaza la transacción completa. Todas las transacciones finalizan cuando un usuario se desconecta. ArcSDESQLExecute utiliza las funciones API de ArcSDE que se proporcionan para iniciar, confirmar y deshacer transacciones.
Si desea controlar cuándo se confirman los cambios en la base de datos, llame al método startTransaction antes de llamar a execute. Así se inicia una transacción y no se confirma ninguna instrucción DML hasta que se llama al método commitTransaction. También se puede producir una confirmación cuando finalice la conexión con ArcSDE (consulte la documentación específica del DBMS para ver cómo trata cada DBMS las desconexiones durante una transacción). Dentro de una transacción, también es posible deshacer cualquier operación DML hasta la confirmación anterior.
Está disponible una propiedad de confirmación automática a intervalos denominada transactionAutoCommit. Se puede utilizar para forzar confirmaciones intermedias una vez modificado un número determinado de entidades.
Consulte la guía de referencia de SQL del DBMS específico para ver ayuda sobre la escritura de instrucciones SQL.
Ejemplos
import sys
import arcpy
try:
# Make data path relative
arcpy.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>
# arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
# Using the second method pass the path to a valid ArcSDE connection file
sde_conn = arcpy.ArcSDESQLExecute(r"data\Connection to GPSERVER3.sde")
# Get the SQL statements, separated by ; from a text string.
sql_statement = arcpy.GetParameterAsText(0)
sql_statement_list = sql_statement.split(";")
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
# For each SQL statement passed in, execute it.
for sql in sql_statement_list:
print("Execute SQL Statement: {0}".format(sql))
try:
# Pass the SQL statement to the database.
sde_return = sde_conn.execute(sql)
except Exception as err:
print(err)
sde_return = 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(sde_return, list):
print("Number of rows returned by query: {0} rows".format(
len(sde_return)))
for row in sde_return:
print(row)
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
else:
# If the return value was not a list, the statement was
# most likely a DDL statement. Check its status.
if sde_return == True:
print("SQL statement: {0} ran successfully.".format(sql))
else:
print("SQL statement: {0} FAILED.".format(sql))
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
except Exception as err:
print(err)
# 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 sys
import arcpy
try:
# Make data path relative (not relevant unless data is moved
# here and paths modified)
arcpy.env.workspace = sys.path[0]
# Column name:value that should be in the record.
sql_values = {"STREET_NAM": "'EUREKA'"}
# Value that is incorrect if found in the above column.
bad_val = "'EREKA'"
#List of tables to look in for the bad value.
tables = ["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>
sde_conn = arcpy.ArcSDESQLExecute("gpserver3", "5151", "#",
"toolbox", "toolbox")
# Using the second method pass the path to a valid ArcSDE connection file
# arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")
for tbl in tables:
print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
for col, val in sql_values.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 {0} for bad data: "
"Column:{1} Value: {2}".format(tbl, col, bad_val))
try:
sql = "select OBJECTID,{0} from {1} where {0} = {2}".format(
col, tbl, bad_val)
print("Attempt to execute SQL Statement: {0}".format(sql))
sde_return = sde_conn.execute(sql)
except Exception as err:
print(err)
sde_return = False
if isinstance(sde_return, list):
if len(sde_return) > 0:
print("Identified {0} rows with incorrect data. Starting "
"transaction for update.".format(len(sde_return)))
# Start the transaction
sde_conn.startTransaction()
print("Transaction started...")
# Perform the update
try:
sql = "update {0} set {1}={2} where {1} = {3}".format(
tbl, col, val, bad_val)
print("Changing bad value: {0} to the good value: "
"{1} using update statement:\n {2}".format(
bad_val, val, sql))
sde_return = sde_conn.execute(sql)
except Exception as err:
print(err)
sde_return = False
# If the update completed successfully, commit the
# changes. If not, rollback.
if sde_return == True:
print("Update statement: \n"
"{0} ran successfully.".format(sql))
# Commit the changes
sde_conn.commitTransaction()
print("Committed Transaction")
# List the changes.
try:
print("Displaying updated rows for "
"visual inspection.")
sql = "select OBJECTID" + \
",{0} from {1} where {0} = {2}".format(
col, tbl, val)
print("Executing SQL Statement: \n{0}".format(sql))
sde_return = sde_conn.execute(sql)
except Exception as err:
print(err)
sde_return = False
if isinstance(sde_return, list):
print("{0} rows".format(len(sde_return)))
for row in sde_return:
print(row)
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
if sde_return == True:
print("SQL statement: \n{0}\n"
"ran successfully.".format(sql))
else:
print("SQL statement: \n{0}\n"
"FAILED.".format(sql))
print("++++++++++++++++++++++++++++++++++++++++\n")
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
print("SQL statement: \n{0}\nFAILED. "
"Rolling back all changes.".format(sql))
# Rollback changes
sde_conn.rollbackTransaction()
print("Rolled back any changes.")
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
print "No records required updating."
# Disconnect and exit
del sde_conn
print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
except Exception as err:
print(err)